Hello!
Im trying to get group by to work in an sql.
Any idea how I should add it to the sql?
Right now the result gets like this
But the result should be like this
select
(select COUNT(O40T2_2.ecarrno) AS Total_packages FROM O40T2 O40T2_2
WHERE O40T2_2.stato40= '50' AND O40T2_2.shortr08 = O40T2.shortr08) AS Total_packages, L54T1.admunit as queue, O40T2., R08T1., R04T1., O04T1.custno o04_custno, O04T1., SHIPTO., ASTRO_VIEW_TBL_OEPARTY.,
CASE
WHEN d50zipc = 11821 THEN 'Zon 1'
WHEN d50zipc = 11822 THEN 'Zon 1'
WHEN d50zipc = 11823 THEN 'Zon 1'
WHEN d50zipc = 11824 THEN 'Zon 1'
WHEN d50zipc = 11825 THEN 'Zon 1'
WHEN d50zipc = 11847 THEN 'Zon 1'
WHEN d50zipc = 11848 THEN 'Zon 1'
WHEN d50zipc = 11849 THEN 'Zon 1'
WHEN d50zipc = 11850 THEN 'Zon 1'
WHEN d50zipc = 11851 THEN 'Zon 1'
WHEN d50zipc = 11852 THEN 'Zon 1'
WHEN d50zipc = 11853 THEN 'Zon 1'
WHEN d50zipc = 11854 THEN 'Zon 1'
WHEN d50zipc = 11855 THEN 'Zon 1'
WHEN d50zipc = 11620 THEN 'Zon 2'
WHEN d50zipc = 11621 THEN 'Zon 2'
WHEN d50zipc = 11622 THEN 'Zon 2'
WHEN d50zipc = 11623 THEN 'Zon 2'
WHEN d50zipc = 11624 THEN 'Zon 2'
WHEN d50zipc = 11625 THEN 'Zon 2'
WHEN d50zipc = 11628 THEN 'Zon 2'
WHEN d50zipc = 11629 THEN 'Zon 2'
WHEN d50zipc = 11630 THEN 'Zon 2'
WHEN d50zipc = 11632 THEN 'Zon 2'
WHEN d50zipc = 11634 THEN 'Zon 2'
WHEN d50zipc = 11636 THEN 'Zon 2'
WHEN d50zipc = 11645 THEN 'Zon 2'
WHEN d50zipc = 11646 THEN 'Zon 2'
WHEN d50zipc = 11729 THEN 'Zon 2'
WHEN d50zipc = 11737 THEN 'Zon 2'
WHEN d50zipc = 11738 THEN 'Zon 2'
WHEN d50zipc = 11820 THEN 'Zon 2'
WHEN d50zipc = 11826 THEN 'Zon 2'
WHEN d50zipc = 11846 THEN 'Zon 2'
WHEN d50zipc = 11872 THEN 'Zon 2'
WHEN d50zipc = 11726 THEN 'Zon 3'
WHEN d50zipc = 11727 THEN 'Zon 3'
WHEN d50zipc = 11728 THEN 'Zon 3'
WHEN d50zipc = 11730 THEN 'Zon 3'
WHEN d50zipc = 11731 THEN 'Zon 3'
WHEN d50zipc = 11732 THEN 'Zon 3'
WHEN d50zipc = 11733 THEN 'Zon 3'
WHEN d50zipc = 11734 THEN 'Zon 3'
WHEN d50zipc = 11735 THEN 'Zon 3'
WHEN d50zipc = 11736 THEN 'Zon 3'
WHEN d50zipc = 11739 THEN 'Zon 3'
WHEN d50zipc = 11740 THEN 'Zon 3'
WHEN d50zipc = 11741 THEN 'Zon 3'
WHEN d50zipc = 11750 THEN 'Zon 3'
WHEN d50zipc = 11631 THEN 'Zon 4'
WHEN d50zipc = 11633 THEN 'Zon 4'
WHEN d50zipc = 11635 THEN 'Zon 4'
WHEN d50zipc = 11637 THEN 'Zon 4'
WHEN d50zipc = 11638 THEN 'Zon 4'
WHEN d50zipc = 11639 THEN 'Zon 4'
WHEN d50zipc = 11640 THEN 'Zon 4'
WHEN d50zipc = 11641 THEN 'Zon 4'
WHEN d50zipc = 11642 THEN 'Zon 4'
WHEN d50zipc = 11643 THEN 'Zon 4'
WHEN d50zipc = 11647 THEN 'Zon 4'
WHEN d50zipc = 11648 THEN 'Zon 4'
WHEN d50zipc = 11661 THEN 'Zon 4'
WHEN d50zipc = 11662 THEN 'Zon 4'
WHEN d50zipc = 11663 THEN 'Zon 4'
WHEN d50zipc = 11664 THEN 'Zon 4'
WHEN d50zipc = 11665 THEN 'Zon 4'
WHEN d50zipc = 11666 THEN 'Zon 4'
WHEN d50zipc = 11667 THEN 'Zon 4'
WHEN d50zipc = 11668 THEN 'Zon 4'
WHEN d50zipc = 11827 THEN 'Zon 5'
WHEN d50zipc = 11828 THEN 'Zon 5'
WHEN d50zipc = 11829 THEN 'Zon 5'
WHEN d50zipc = 11830 THEN 'Zon 5'
WHEN d50zipc = 11842 THEN 'Zon 5'
WHEN d50zipc = 11856 THEN 'Zon 5'
WHEN d50zipc = 11857 THEN 'Zon 5'
WHEN d50zipc = 11858 THEN 'Zon 5'
WHEN d50zipc = 11859 THEN 'Zon 5'
WHEN d50zipc = 11860 THEN 'Zon 5'
WHEN d50zipc = 11861 THEN 'Zon 5'
WHEN d50zipc = 11862 THEN 'Zon 5'
WHEN d50zipc = 11863 THEN 'Zon 5'
WHEN d50zipc = 11864 THEN 'Zon 5'
WHEN d50zipc = 11865 THEN 'Zon 5'
WHEN d50zipc = 11866 THEN 'Zon 5'
WHEN d50zipc = 11867 THEN 'Zon 5'
WHEN d50zipc = 11869 THEN 'Zon 5'
WHEN d50zipc = 11127 THEN 'Zon 6'
WHEN d50zipc = 11128 THEN 'Zon 6'
WHEN d50zipc = 11129 THEN 'Zon 6'
WHEN d50zipc = 11130 THEN 'Zon 6'
WHEN d50zipc = 11131 THEN 'Zon 6'
END AS Zoner
from O40T2
left join L54T1 on L54T1.shortr08 = O40T2.shortr08
left join R08T1 on R08T1.shortr08 = O40T2.shortr08
left join R04T1 on R04T1.routeno = R08T1.routeno
left join O06T1 on O06T1.shorto06 = O40T2.shorto06
left join O04T1 on O04T1.shorto04 = O06T1.shorto04
left join (select d13obj td13obj, sysshort tsysshort, roletype troletype,
d10name1 tname1, d50adrs1 taddressline1, d50adrs2 taddressline2, d50adrs3 taddressline3, d50zipc tpostal, d50city tcity, d50cntry tcountry
from D13T1
join D10T1 on D10T1.shortd10=D13T1.shortd10
join D50T1 on D50T1.adrid= D10T1.adrid ) SHIPTO
on SHIPTO.tsysshort=O40T2.ocarrno and SHIPTO.td13obj=170 and SHIPTO.troletype=5
left join ASTRO_VIEW_TBL_OEPARTY ON ASTRO_VIEW_TBL_OEPARTY.d13obj = 20 and ASTRO_VIEW_TBL_OEPARTY.sysshort = O06T1.shorto04
where O40T2.stato40= 50