Sum with multiple Where conditions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Sum with multiple Where conditions

Author

Topic

mad marcus
Starting Member

12 Posts

Posted - 2010-04-18 : 19:37:10

Hi all,

I have the following query generating a report (which works fine).

SELECT TOP (100) PERCENT ROW_NUMBER() OVER (ORDER BY PA.PrimaryActivityId) AS ID, PA.PrimaryActivityId,
PA.ParentId, PA.ActivityDetails, PA.Coordinator, COUNT(PA.PrimaryActivityId) AS NumKpis, Kpi.Status
FROM Kpi LEFT OUTER JOIN PA ON Kpi.PrimaryActivityId = PA.PrimaryActivityId
GROUP BY PA.PrimaryActivityId, PA.ParentId, PA.ActivityDetails, PA.Coordinator, Kpi.Status

But now I need to add 3 new totals columns for each row that includes the total number of KPI's where Status=1, Status=2 and Status=3 respectively.

Any help in how to tackle this would be much appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-18 : 19:49:08

Show us a data example to make your problem clear.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

mad marcus
Starting Member

12 Posts

Posted - 2010-04-18 : 20:51:15

Actually I have just discovered the original query isn't working quite right either. A sample is below. I have truncated the column headings a bit and removed some rows but you should get the idea.

ID PrActId Parent Details Coord NumKpis Status
1 2 A2 blah blah 5 7 1
2 3 A3 blah blah 5 3 1
7 17 A8 blah blah 5 13 1
8 18 A9 blah blah 5 10 1
9 19 A10 blah blah 8 1 1
10 20 A11 blah blah 5 7 1
11 22 A13 blah blah 5 7 1
12 23 A14 blah blah 5 2 1
13 23 A14 blah blah 5 1 3
14 24 A15 blah blah 8 5 2

The problem with this is that ID's 12 and 13 (both with the same PrActId of 23 and Parent of A14 should appear as one row and a NumKpis of 3 which is the total of both, I assume that because they have different Status values it appears as 2 rows. This seems related to the original question I had where I need to generate something more like the data below. I have removed a couple of the columns not relevent to make it easier to follow.

ID PrActId Parent Details NumKpis Pending Overdue Complete
1 2 A2 blah blah 7 7 0 0
2 3 A3 blah blah 3 3 0 0
7 17 A8 blah blah 13 13 0 0
8 18 A9 blah blah 10 10 0 0
9 19 A10 blah blah 1 1 0 0
10 20 A11 blah blah 7 5 1 1
11 22 A13 blah blah 7 3 2 2
12 23 A14 blah blah 3 1 0 2
13 24 A15 blah blah 5 0 0 5

The last 3 summary columns can be filtered on the Status to get which records are Pending, Overdue or Complete but the column itself doesn't need to be displayed.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-18 : 21:37:07

you can use PIVOT

try


SELECT	row_number() over (order by PrimaryActivityId) as ID,
	PrimaryActivityId,
	ParentId,
	ActivityDetails,
	Coordinator,
	NumKpis,
	[1] as Pending, 
	[2] as Overdue,
	[3] as Complete
FROM
(
	SELECT	
		PA.PrimaryActivityId,
		PA.ParentId, 
		PA.ActivityDetails, 
		PA.Coordinator, 
		COUNT(PA.PrimaryActivityId) AS NumKpis, 
		Kpi.Status
	FROM 	
		Kpi 
		LEFT OUTER JOIN PA 	ON 	Kpi.PrimaryActivityId = PA.PrimaryActivityId
	GROUP BY 
		PA.PrimaryActivityId, 
		PA.ParentId, 
		PA.ActivityDetails, 
		PA.Coordinator, 
		Kpi.Status
) k
pivot
(
	sum(NumKpis)
	for Status in ([1], [2], [3])
) p

KH
[spoiler]Time is always against us[/spoiler]

mad marcus
Starting Member

12 Posts

Posted - 2010-04-18 : 22:21:21

Thanks khtan I tried that but I get an - Invalid column name 'NumKpis' - error message

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-18 : 22:32:12

edited my last post

KH
[spoiler]Time is always against us[/spoiler]

mad marcus
Starting Member

12 Posts

Posted - 2010-04-18 : 23:11:17

Thanks khtan,

That fixed the error and it now returns the result we need, but is there a way we can still include a total column that adds the results from all 3?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-18 : 23:12:35

add to the select clause

[1] + [2] + [3] as Total

KH
[spoiler]Time is always against us[/spoiler]

mad marcus
Starting Member

12 Posts

Posted - 2010-04-18 : 23:51:47

Many thanks khtan,

Once I had allowed for nulls that worked great.

I have never used pivot before. So much to learn and so little time.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-18 : 23:52:54

quote:
Originally posted by mad marcus

Many thanks khtan,

Once I had allowed for nulls that worked great.

I have never used pivot before. So much to learn and so little time.

yes. I missed out that part.

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources