SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 aggregate function on an expression error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Andy2358
Starting Member

4 Posts

Posted - 05/03/2013 :  19:02:56  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

276 Posts

Posted - 05/04/2013 :  06:41:29  Show Profile  Reply with Quote
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

Sweden
29908 Posts

Posted - 05/04/2013 :  12:44:42  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 05/05/2013 :  12:41:38  Show Profile  Reply with Quote
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 http://www.flickr.com/photos/67629072@N05/8710019733/in/photostream

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
Flowing Fount of Yak Knowledge

3322 Posts

Posted - 05/05/2013 :  21:04:59  Show Profile  Reply with Quote
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 - 05/06/2013 :  05:21:52  Show Profile  Reply with Quote
Thanks James. Spot on.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000