Question 113068 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to use GROUP BY on columns with SUM and CASE WHEN

I have a query like this

Select a.ordernum, a.region, c.area, a.branchname, a.stagename,
sum(case when b.entrydatetime is not null THEN 1 ELSE 0 END) as 'Total Count',
sum(case when DATEDIFF(day, b.entrydatetime, GETDATE()) <=1 THEN 1 ELSE 0 END) as 'WITHIN 1 DAY',
sum(case when DATEDIFF(day, b.entrydatetime, GETDATE()) >1 AND DATEDIFF(day, b.entrydatetime, GETDATE()) <=2 
THEN 1 ELSE 0 END) as 'WITHIN 2 DAYS'
FROM DATAtBLE a, datatbleb b, datatblec c where a.ordernum = b.ordernumber and a.ordernum = c.ordernum 
and a.region in ('India,'US','Pakistan') and c.area in ('Chennai','West Bengal','New York') and a.branchname
('Brooklyn','Navi Mumbai')
order by a.region,  c.area,  a.branchname, a.stagename,a.ordernum
group by a.region,  c.area,  a.branchname, a.stagename,a.ordernum

And I get an output like this

ordernum region area branchname stagename Total Count WITHIN 1 DAY WITHIN 2 DAYS
000000001 India NYC Navi Mumbai Ordered 1 1 0
000000002 India NYC Navi Mumbai Ordered 1 0 1
000000003 India NYC Navi Mumbai Shipped 1 1 0
000000004 India NYC Navi Mumbai Shipped 1 0 1

But the output am expecting is

region area branchname stagename Total Count WITHIN 1 DAY WITHIN 2 DAYS
India NYC Navi Mumbai Ordered 2 1 1
India NYC Navi Mumbai Shipped 2 1 1

You see I want the rows to be summed up based on the stagename and do not want individual rows for each order number. How can I my query be tweaked to get this output am expecting?

Tags:

2

Answers


  1. 0 
    Select a.ordernum, a.region, c.area, a.branchname, a.stagename,
sum(case when b.entrydatetime is not null THEN 1 ELSE 0 END) as 'Total Count',
sum(case when DATEDIFF(day, b.entrydatetime, GETDATE()) <=1 THEN 1 ELSE 0 END) as 'WITHIN 1 DAY',
sum(case when DATEDIFF(day, b.entrydatetime, GETDATE()) >1 AND DATEDIFF(day, b.entrydatetime, GETDATE()) <=2 
THEN 1 ELSE 0 END) as 'WITHIN 2 DAYS'
FROM DATAtBLE a, datatbleb b, datatblec c where a.ordernum = b.ordernumber and a.ordernum = c.ordernum 
and a.region in ('India,'US','Pakistan') and c.area in ('Chennai','West Bengal','New York') and a.branchname
('Brooklyn','Navi Mumbai')
order by a.region,  c.area,  a.branchname, a.stagename,a.ordernum
group by a.region,  c.area,  a.branchname, a.stagename,a.ordernum

    change, maybe will be works

    COUNT(case when b.entrydatetime is not null THEN 1 ELSE 0 END) as 'Total Count',

GROUP BY TotalCount ...
    Login or Signup to reply.
  2. 0

    Please try removing "a.ordernum" in select list, order by and group by as shown below. It will give you the expected output. Thank you!

    Select a.region, c.area, a.branchname, a.stagename, 
sum(case when b.entrydatetime is not null THEN 1 ELSE 0 END) as 'Total Count', 
sum(case when DATEDIFF(day, b.entrydatetime, GETDATE()) <=1 THEN 1 ELSE 0 END) as 'WITHIN 1 DAY', 
sum(case when DATEDIFF(day, b.entrydatetime, GETDATE()) >1 AND DATEDIFF(day, b.entrydatetime, GETDATE()) <=2 THEN 1 ELSE 0 END) as 'WITHIN 2 DAYS' 
FROM DATAtBLE a, datatbleb b, datatblec c 
where a.ordernum = b.ordernumber and a.ordernum = c.ordernum and a.region in ('India','US','Pakistan') 
and c.area in ('Chennai','West Bengal','New York') and a.branchname ('Brooklyn','Navi Mumbai') 
order by a.region,  c.area,  a.branchname, a.stagename group by a.region,  c.area,  a.branchname, a.stagename
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search