Sort in reports

[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

[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.

[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

[kknm]

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

[kev1n]

How does the sorting happen? I don't see a ORDER 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

[admin]

Guys,

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