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)
 SQL Aggregates help

Author  Topic 

cipriani1984
Constraint Violating Yak Guru

304 Posts
Posted - 2008-11-04 : 07:21:44
Hi,

I have this query,

SELECT t.student_id, t.week_no
FROM dbo.StudAtt T
CROSS APPLY (SELECT TOP 3 week_no
FROM dbo.StudAtt
GROUP BY student_id, acad_period, week_no
HAVING student_id = t.student_id
AND week_no < t.week_no
AND (acad_period = '08/09')
AND (CASE WHEN (SUM(t.Actual)) > 0 AND (SUM(t.Meets) > 0) THEN ((SUM(t.Actual) * 1.0) / (SUM(t.Meets) * 1.0) * 100)
ELSE '0' END = 0)
ORDER BY week_no DESC) t1
OUTER APPLY (SELECT COUNT(week_no) AS PresentCount
FROM dbo.StudAtt
GROUP BY student_id, acad_period
HAVING student_id = t.student_id
AND week_no >= t1.week_no
AND week_no <= t.week_no
AND (acad_period = '08/09')
AND (CASE WHEN (SUM(t.Actual)) > 0 AND (SUM(t.Meets) > 0) THEN ((SUM(t.Actual) * 1.0) / (SUM(t.Meets) * 1.0) * 100)
ELSE '0' END > 0) ) t2
GROUP BY t.student_id, t.acad_period, t.week_no
HAVING (t.acad_period = '08/09') AND (CASE WHEN (SUM(t.Actual)) > 0 AND (SUM(t.Meets) > 0) THEN ((SUM(t.Actual) * 1.0) / (SUM(t.Meets) * 1.0) * 100)
ELSE '0' END = 0) AND ISNULL(t2.PresentCount, 0) = 0
ORDER BY t.student_id, t.week_no

And i get the following error:

Aggregates on the right side of an APPLY cannot reference columns from the left side.

Any help/advice on what that means?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-11-04 : 07:27:24
You need to first agrregate the columns on left part and form a dervied table out of them. Then take CROSS APPLY with this derived table to get the reqd result.
Go to Top of Page

cipriani1984
Constraint Violating Yak Guru

304 Posts
Posted - 2008-11-04 : 08:31:03
any chance of an example?

quote:
Originally posted by visakh16

You need to first agrregate the columns on left part and form a dervied table out of them. Then take CROSS APPLY with this derived table to get the reqd result.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-11-04 : 11:08:03
Do you know what your HAVING part is doing?
HAVING	CASE
		WHEN SUM(t.Actual) > 0 AND SUM(t.Meets) > 0 THEN 100.0 * SUM(t.Actual) / SUM(t.Meets)
		ELSE 0
	END = 0
If SUM(t.Actual) and SUM(t.Meets) both are positive then you divide those two and you only want the record back if the division is equal to 0. Then only division that is zero is when the upper value is zero.
In all other cases, you use zero to check against zero.
This can be simplified as
HAVING	CASE
		WHEN SUM(t.Actual) > 0 THEN SUM(t.Actual)
		ELSE 0
	END = 0
But then again, if SUM(t.Actual) is GREATER than zero then you check for SUM(t.Actual) is EQUAL to zero, which will NEVER happen! First CASE will never evaluate to true.

So this is what you can use instead of original CASE
HAVING	SUM(t.Actual) <= 0


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

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-11-04 : 11:15:47
I think this rewrite does the same thing as your original monster query
SELECT		studentID,
		weekNo
FROM		(
			SELECT	t.studentID,
				t.weekNo,
				t.Actual
				ROW_NUMBER() OVER (PARTITION BY t.studentID ORDER BY t.weekNo DESC) AS recID,
				COUNT(*) OVER (PARTITION BY t.studentID) AS pCount
			FROM	dbo.studAtt AS t
			WHERE	t.acad_period = '08/09'
		) AS w
WHERE		recID <= 3
GROUP BY	student_id,
		week_no
HAVING		SUM(Actual) <= 0
		AND MAX(pCount) = 0


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

cipriani1984
Constraint Violating Yak Guru

304 Posts
Posted - 2008-11-04 : 11:17:19
Ok, ive changed the query to:

SELECT t.student_id, t.week_no
FROM dbo.StudAtt T
CROSS APPLY (SELECT TOP 3 week_no
FROM dbo.StudAtt
GROUP BY student_id, acad_period, week_no
HAVING student_id = t.student_id
AND week_no < t.week_no
AND (acad_period = '08/09')
AND SUM(t.Actual) <= 0
ORDER BY week_no DESC) t1
OUTER APPLY (SELECT COUNT(week_no) AS PresentCount
FROM dbo.StudAtt
GROUP BY student_id, acad_period
HAVING student_id = t.student_id
AND week_no >= t1.week_no
AND week_no <= t.week_no
AND (acad_period = '08/09')
AND SUM(t.Actual) > 0 ) t2
GROUP BY t.student_id, t.acad_period, t.week_no
HAVING (t.acad_period = '08/09') AND SUM(t.Actual) <= 0 AND ISNULL(t2.PresentCount, 0) = 0
ORDER BY t.student_id, t.week_no

and still get aggregates error;
Aggregates on the right side of an APPLY cannot reference columns from the left side.

quote:
Originally posted by Peso

Do you know what your HAVING part is doing?
HAVING	CASE
		WHEN SUM(t.Actual) > 0 AND SUM(t.Meets) > 0 THEN 100.0 * SUM(t.Actual) / SUM(t.Meets)
		ELSE 0
	END = 0
If SUM(t.Actual) and SUM(t.Meets) both are positive then you divide those two and you only want the record back if the division is equal to 0. Then only division that is zero is when the upper value is zero.
In all other cases, you use zero to check against zero.
This can be simplified as
HAVING	CASE
		WHEN SUM(t.Actual) > 0 THEN SUM(t.Actual)
		ELSE 0
	END = 0
But then again, if SUM(t.Actual) is GREATER than zero then you check for SUM(t.Actual) is EQUAL to zero, which will NEVER happen! First CASE will never evaluate to true.

So this is what you can use instead of original CASE
HAVING	SUM(t.Actual) <= 0


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

cipriani1984
Constraint Violating Yak Guru

304 Posts
Posted - 2008-11-04 : 11:25:19
I get very funny error with this query, line 7 '('

quote:
Originally posted by Peso

I think this rewrite does the same thing as your original monster query
SELECT		studentID,
		weekNo
FROM		(
			SELECT	t.studentID,
				t.weekNo,
				t.Actual
				ROW_NUMBER() OVER (PARTITION BY t.studentID ORDER BY t.weekNo DESC) AS recID,
				COUNT(*) OVER (PARTITION BY t.studentID) AS pCount
			FROM	dbo.studAtt AS t
			WHERE	t.acad_period = '08/09'
		) AS w
WHERE		recID <= 3
GROUP BY	student_id,
		week_no
HAVING		SUM(Actual) <= 0
		AND MAX(pCount) = 0


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

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-11-04 : 11:29:16
Put a comma after t.Actual in the derived table w.
And try again.


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

cipriani1984
Constraint Violating Yak Guru

304 Posts
Posted - 2008-11-04 : 11:32:41
thanks,

the aim of my query was to get the students who have been absent for more than 3 weeks in a row.

quote:
Originally posted by Peso

Put a comma after t.Actual in the derived table w.
And try again.


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

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-11-04 : 11:36:15
Oh, so this is really a continuation of this topic?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=113717


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

cipriani1984
Constraint Violating Yak Guru

304 Posts
Posted - 2008-11-04 : 11:44:39
Yup but i kept getting no more help :(

Is there any way to apply some logic thatll identify students that have 0 SUM(Actual) = 0 for 4 consecutive weeks?

quote:
Originally posted by Peso

Oh, so this is really a continuation of this topic?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=113717


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

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-11-04 : 11:47:10
See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=113717
I have posted two different solutions there.

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

cipriani1984
Constraint Violating Yak Guru

304 Posts
Posted - 2008-11-07 : 09:00:53
Ive changed your query to the following below:

But I get a operand type char is invalid sum operator, ive played around with the grouping and stil get same error, any advice?

DECLARE @Sample TABLE
(
student_id CHAR(15),
week_no TINYINT,
Actual CHAR(1)
)

INSERT @Sample
(
student_id,
week_no,
Actual
)

SELECT dbo.stmbiogr.student_id, dbo.StudAtt.week_no, SUM(StudAtt.Actual) AS Actual
FROM dbo.stmbiogr INNER JOIN
dbo.StudAtt ON dbo.stmbiogr.student_id = dbo.StudAtt.student_id
WHERE (dbo.StudAtt.acad_period = '08/09')
GROUP BY dbo.stmbiogr.student_id, dbo.StudAtt.week_no
HAVING (SUM(StudAtt.Actual) = 0)

SELECT DISTINCT s1.student_id
FROM @Sample AS s1
INNER JOIN @Sample AS s2 ON s2.student_id = s1.student_id
AND s2.week_no = s1.week_no + 1
AND (SUM(s2.Actual) = 0)
INNER JOIN @Sample AS s3 ON s3.student_id = s2.student_id
AND s3.week_no = s2.week_no + 1
AND (SUM(s3.Actual) = 0)
WHERE (SUM(s1.Actual) = 0)

quote:
Originally posted by Peso

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=113717
I have posted two different solutions there.

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-11-07 : 09:11:35
you cant sum on char datatype. use MAX() or MIN() depending on your requirement instead of SUM()
Go to Top of Page

cipriani1984
Constraint Violating Yak Guru

304 Posts
Posted - 2008-11-07 : 09:14:49
But max and min doesnt give the same value as sum do they?

quote:
Originally posted by visakh16

you cant sum on char datatype. use MAX() or MIN() depending on your requirement instead of SUM()
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-11-07 : 09:29:08
just posted a suggestion here. is this what you want?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=113717
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-11-07 : 09:31:16
We have no idea that ACTUAL represents.


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

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-11-07 : 09:34:00
See http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-11-07 : 09:37:22
quote:
Originally posted by Peso

We have no idea that ACTUAL represents.


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

Seeing the datatype (CHAR(1)) i assume its Absent bit, though guarantee on it
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-11-07 : 09:41:05
In other topic there were a reference to an "absent" column, which was "y" or "n".


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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-11-07 : 09:45:19
quote:
Originally posted by Peso

In other topic there were a reference to an "absent" column, which was "y" or "n".


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

yeah...seems like the original column name was Actual rather than absent
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site