SQL group by, sum, total

Question

Post reply

SQL group by, sum, total

aas1985

SSC Journeyman

Points: 86
More actions
November 28, 2016 at 11:22 am

#323312

Can someone help with the query?
I have a table here:
CREATE TABLE [dbo].[aas](
[id] [int] IDENTITY(1,1) NOT NULL,
[Status] [nchar](10) NULL,
[price] [int] NOT NULL
) ON [PRIMARY]
GO
insert into [dbo].[aas] ([Status], [price])
values ('a', 23)
insert into [dbo].[aas] ([Status], [price])
values ('a', 23)
insert into [dbo].[aas] ([Status], [price])
values ('a', 23)
insert into [dbo].[aas] ([Status], [price])
values ('b', 4)
insert into [dbo].[aas] ([Status], [price])
values ('b', 4)
insert into [dbo].[aas] ([Status], [price])
values ('b', 4)
insert into [dbo].[aas] ([Status], [price])
values ('b', 4)
insert into [dbo].[aas] ([Status], [price])
values ('c', 8)
insert into [dbo].[aas] ([Status], [price])
values ('c', 8)
insert into [dbo].[aas] ([Status], [price])
values ('c', 8)
insert into [dbo].[aas] ([Status], [price])
values ('c', 8)
insert into [dbo].[aas] ([Status], [price])
values ('c', 8)
insert into [dbo].[aas] ([Status], [price])
values ('y', 2)
insert into [dbo].[aas] ([Status], [price])
values ('y', 2)
insert into [dbo].[aas] ([Status], [price])
values ('y', 2)
insert into [dbo].[aas] ([Status], [price])
values ('y', 2)
insert into [dbo].[aas] ([Status], [price])
values ('y', 2)
insert into [dbo].[aas] ([Status], [price])
values ('y', 2)
insert into [dbo].[aas] ([Status], [price])
values ('y', 2)
insert into [dbo].[aas] ([Status], [price])
values ('y', 2)
insert into [dbo].[aas] ([Status], [price])
values ('y', 2)
insert into [dbo].[aas] ([Status], [price])
values ('y', 2)
insert into [dbo].[aas] ([Status], [price])
values ('y', 2)
insert into [dbo].[aas] ([Status], [price])
values ('y', 2)
I am expecting an output like this:
TotalA TotalPriceA TotalB TotalPriceB TotalC TotalPriceC TotalY TotalPriceY
3 69 4 16 5 40 12 24
But this query returns redundant datas
select
COUNT(CASE WHEN [status] = 'a' THEN 1 ELSE 0 END) as [TotalA],
case when [status] = 'a' then
sum(price)
else 0
END as [TotalpriceA],
COUNT(CASE WHEN [status] = 'b' THEN 1 ELSE 0 END) as [TotalB],
case when [status] = 'b' then
sum(price)
else 0
END as [TotalpriceB],
COUNT(CASE WHEN [status] = 'c' THEN 1 ELSE 0 END) as [TotalC],
case when [status] = 'c' then
sum(price)
else 0
END as [TotalpriceC],
COUNT(CASE WHEN [status] = 'y' THEN 1 ELSE 0 END) as [TotalY],
case when [status] = 'y' then
sum(price)
else 0
END as [TotalpriceY]
from [dbo].[aas]
group by [status]
Any suggestions?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

select

COUNT(CASE WHEN [status] = 'a' THEN 1 END) as [TotalA],

sum(case when [status] = 'a' then price end) as [TotalpriceA],

COUNT(CASE WHEN [status] = 'b' THEN 1 END) as [TotalB],

sum(case when [status] = 'b' then price end) as [TotalpriceB],

COUNT(CASE WHEN [status] = 'c' THEN 1 END) as [TotalC],

sum(case when [status] = 'c' then price end) as [TotalpriceC],

COUNT(CASE WHEN [status] = 'y' THEN 1 END) as [TotalY],

sum(case when [status] = 'y' then price end) as [TotalpriceY]

from [dbo].[aas]

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

drew.allen SSC Guru Points: 76988 More actions · Answer 2

Remove the GROUP BY. You're getting totals for every status when you only want the grand total.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

aas1985 SSC Journeyman Points: 86 More actions · Answer 3

aas1985

SSC Journeyman

Points: 86

November 28, 2016 at 1:41 pm

#1914819

Thanks mate. It helped a lot