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

But the output am expecting is

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?

CodePudding user response：

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

CodePudding user response：

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