Page 1 of 1
Sort in reports
Posted: Tue Jun 23, 2020 12:12 pm
by kknm
Why sorting does not work in reports ?
SELECT ves_num,
SUM(ves_kol) AS sum_kol,
TRUNCATE(SUM(ves_tn)/1000,2) AS sum_tn
FROM vesyauto
JOIN tabel ON vesyauto.ves_tab = tabel.tabel_id
WHERE MONTH(tabel.tab_data)='06' AND YEAR(tabel.tab_data)='2020'
GROUP BY ves_num ASC
UNION ALL
SELECT 'ИТОГО:', SUM(ves_kol),TRUNCATE(SUM(ves_tn)/1000,2)
FROM vesyauto
ORDER BY ves_num ASC
]
ves.png
Re: Sort in reports
Posted: Tue Jun 23, 2020 12:38 pm
by Janusz
In such cases I am defining view directly on DB level with phpmyadmin and after I refer to the view as to any other table. For me it's very conveniant and easy to debug.
Re: Sort in reports
Posted: Tue Jun 23, 2020 1:09 pm
by kev1n
Code: Select all
The ORDER BY is just applied to the 2nd select. Try this:
Select *
from
(
SELECT ves_num,
SUM(ves_kol) AS sum_kol,
TRUNCATE(SUM(ves_tn)/1000,2) AS sum_tn
FROM vesyauto
JOIN tabel ON vesyauto.ves_tab = tabel.tabel_id
WHERE MONTH(tabel.tab_data)='06' AND YEAR(tabel.tab_data)='2020'
GROUP BY ves_num ASC
UNION ALL
SELECT 'ИТОГО:', SUM(ves_kol),TRUNCATE(SUM(ves_tn)/1000,2)
FROM vesyauto
) results
ORDER BY ves_num ASC
Re: Sort in reports
Posted: Tue Jun 23, 2020 3:44 pm
by kknm
kev1n wrote:Code: Select all
The ORDER BY is just applied to the 2nd select. Try this:
Select *
from
(
SELECT ves_num,
SUM(ves_kol) AS sum_kol,
TRUNCATE(SUM(ves_tn)/1000,2) AS sum_tn
FROM vesyauto
JOIN tabel ON vesyauto.ves_tab = tabel.tabel_id
WHERE MONTH(tabel.tab_data)='06' AND YEAR(tabel.tab_data)='2020'
GROUP BY ves_num ASC
UNION ALL
SELECT 'ИТОГО:', SUM(ves_kol),TRUNCATE(SUM(ves_tn)/1000,2)
FROM vesyauto
) results
ORDER BY ves_num ASC
That doesn't work either.
ORDER BY does not work together with UNION in phpmyadmin, too.
This is how it works.
Code: Select all
SELECT ves_num,
SUM(ves_kol) AS sum_kol,
TRUNCATE(SUM(ves_tn)/1000,2) AS sum_tn
FROM vesyauto
JOIN tabel ON vesyauto.ves_tab = tabel.tabel_id
WHERE MONTH(tabel.tab_data)='06' AND YEAR(tabel.tab_data)='2020'
GROUP BY ves_num ASC
UNION ALL
SELECT 'ИТОГО:', SUM(ves_kol),TRUNCATE(SUM(ves_tn)/1000,2)
FROM vesyauto
Re: Sort in reports
Posted: Tue Jun 23, 2020 4:28 pm
by kev1n
How does the sorting happen? I don't see a ORDER BY.
Re: Sort in reports
Posted: Tue Jun 23, 2020 4:56 pm
by Janusz
ORDER BY does not work together with UNION in phpmyadmin, too.
https://stackoverflow.com/questions/353 ... l/32848661
the above maybe can help
or the simpler solution: make second view which will make just the sorting of the first view
maybe crazy but works
Re: Sort in reports
Posted: Sun Jun 28, 2020 10:58 pm
by admin
Guys,
kknm wrote:Why sorting does not work in reports ?
When a Report is run it takes the data and orders it by the
Group Properties in the Report.
gb2.png
This Report is ordered by ves_num which is sorted as a
String not a
Number.
ves.png
Steven