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.
Author |
Topic |
Andy2358
Starting Member
4 Posts |
Posted - 2013-05-03 : 19:02:56
|
Hi all, I've tried the following but get the aggregate function error above. Ive looked at a couple of work arounds (a derived table?) but couldnt seem to apply all the guidance. Can anyone help please? select sum(((Case when(SUM(case when datediff(dd,(isnull(Behaviour.BehaviourDate,GETDATE()+1)),GETDATE())<0 then 0 else 1 End)) =0 then 4 when (SUM(case when datediff(dd,(isnull(Behaviour.BehaviourDate,GETDATE()+1)),GETDATE())<0 then 0 else 1 End)) =1 then 2 when (SUM(case when datediff(dd,(isnull(Behaviour.BehaviourDate,GETDATE()+1)),GETDATE())<0 then 0 else 1 End)) =2 then 1 else 0 End)))as 'pts'from students_current_table Left join Behaviour on students_current_table.Person_ID=Behaviour.Person_ID and(Behaviour.Positive<>'True') and (datediff(dd,BehaviourDate,'2012-11-21')<7)where (students_current_table.House = 'Warren')group by Students_Current_table.Person_ID, Reg |
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2013-05-04 : 06:41:29
|
sum within a sum is a no go.Can you describe exactly that you are trying to accomplish?Additionally: table specs and example data will often help. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-05-04 : 12:44:42
|
Something like this?;WITH cteSource(PersonID, Reg, theSum)AS ( SELECT sct.Person_ID, {Table alias here}.Reg, CASE WHEN DATEDIFF(DAY, b.BehaviourDate, GETDATE()) < 0 THEN 0 ELSE 1 END AS theSum FROM dbo.Students_Current_Table AS sct LEFT JOIN dbo.Behaviour AS b ON b.Person_ID = sct.Person_ID AND b.Positive <> 'True' AND b.BehaviourDate >= DATEADD(DAY, -7, '20121121') WHERE sct.House = 'Warren')SELECT Person_ID, Reg, SUM(CASE WHEN theSum = 0 THEN 4 WHEN theSum = 1 THEN 2 WHEN theSum = 2 THEN 1 ELSE 0 END) AS PtsFROM cteSouceGROUP BY Person_ID, Reg; N 56°04'39.26"E 12°55'05.63" |
|
|
Andy2358
Starting Member
4 Posts |
Posted - 2013-05-05 : 12:41:38
|
Thanks for this SwePeso. Its really useful and pushes my SQL knowledge on quite a way.It almost does the job....quote: Originally posted by SwePeso Something like this?;WITH cteSource(Person_ID, Reg, theSum)AS ( SELECT sct.Person_ID, sct.Reg, CASE WHEN DATEDIFF(DAY, b.BehaviourDate, GETDATE()) < 0 THEN 0 ELSE 1 END AS theSum FROM dbo.Students_Current_Table AS sct LEFT JOIN dbo.Behaviour AS b ON b.Person_ID = sct.Person_ID AND b.Positive <> 'True' AND b.BehaviourDate >= DATEADD(DAY, -7, '20121121') WHERE sct.House = 'Warren')SELECT Person_ID, Reg, SUM(CASE WHEN theSum = 0 THEN 4 WHEN theSum = 1 THEN 2 WHEN theSum = 2 THEN 1 ELSE 0 END) AS PtsFROM cteSouceGROUP BY Person_ID, Reg; N 56°04'39.26"E 12°55'05.63"However, this produces three columns of data:Person_IDRegand Ptswhich is the number of points that each person has.as you can see [url]http://www.flickr.com/photos/67629072@N05/8710019733/in/photostream[/URL]What Im looking for is one list of Reg1Reg2Reg3etc with the total points for that group.
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-05 : 21:04:59
|
Remove the Person_ID column from the final select and group by:.......)SELECT Person_ID, Reg, SUM(CASE WHEN theSum = 0 THEN 4 WHEN theSum = 1 THEN 2 WHEN theSum = 2 THEN 1 ELSE 0 END) AS PtsFROM cteSouceGROUP BY Person_ID, Reg; |
|
|
Andy2358
Starting Member
4 Posts |
Posted - 2013-05-06 : 05:21:52
|
Thanks James. Spot on. |
|
|
|
|
|
|
|