November 28, 2016 at 11:22 am
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
November 28, 2016 at 11:40 am
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/61537November 28, 2016 at 11:40 am
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
November 28, 2016 at 1:41 pm
Thanks mate. It helped a lot
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy