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
 General SQL Server Forums
 New to SQL Server Programming
 aggregate function on an expression error

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.
Go to Top of Page

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 Pts
FROM cteSouce
GROUP BY Person_ID,
Reg;



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

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 Pts
FROM cteSouce
GROUP BY Person_ID,
Reg;



N 56°04'39.26"
E 12°55'05.63"


However, this produces three columns of data:

Person_ID
Reg
and Pts

which 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
Reg1
Reg2
Reg3

etc with the total points for that group.

Go to Top of Page

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 Pts
FROM cteSouce
GROUP BY Person_ID,
Reg;
Go to Top of Page

Andy2358
Starting Member

4 Posts

Posted - 2013-05-06 : 05:21:52
Thanks James. Spot on.
Go to Top of Page
   

- Advertisement -