Select Count Group By Error message

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)

Select Count Group By Error message

Author

Topic

hamdanian
Starting Member

4 Posts

Posted - 2010-06-23 : 17:33:44

Campus Numberaccts NumberDaysOutstandingAvgDays
C1
C2
C3

Total

I'm trying to create the above table in a stored procedure but I'm getting two error messages:

1st error message 1st line: Cannot perform an aggregate function on an expression containing an aggregate or a subquery. in 1st line(COUNT(Case B.Descrip When 'Ready to Review' THEN B.Descrip ELSE Null END) AS NumberAccts

2nd error message 8th line: Invalid column name 'NumberDaysOutstanding'. Invalid column name 'NumberAccts'.

Select A.Descrip as Camp, COUNT(Case B.Descrip When 'Ready to Review' THEN B.Descrip ELSE Null END) AS NumberAccts,
MAX(DateDiff(day,(SELECT cast(convert(char(12),max(datelstmod) ,101) as datetime)
FROM C where C.RecordID = E.FaStudentAyID
and Columnname like 'Package Status'),GetDate())) AS NumberDaysOutstanding, NumberDaysOutstanding/NumberAccts AS Avg_Days

FROM D JOIN A ON D.SyCampusID = A.SyCampusID
JOIN E ON D.AdEnrollID = E.AdEnrollID
JOIN B ON B.FaPackStatusID = E.FaPackStatusID
JOIN C ON C.RecordID = E.FaStudentAyID
Group BY A.Descrip

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-24 : 03:06:54

Wow, this query was quite messy.

COUNT(Case B.Descrip When 'Ready to Review' THEN B.Descrip ELSE Null END) AS NumberAccts
should be changed to
SUM(Case When B.Descrip = 'Ready to Review' THEN 1 ELSE 0 END) AS NumberAccts

The rest of the query ill have to be explained forst...I don't get it.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-24 : 11:14:27

Perhaps some sample data and expected output would help. Here is a link that describes how to prepare your question so we can answer it faster:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

hamdanian
Starting Member

4 Posts

Posted - 2010-06-24 : 16:09:34

This Worked thanks!! but how do I add one extra column which divides column c with column b. In the last selection NumberDaysOutstanding/NumberAccts AS Avg did not work.

Select SyCampus.Descrip as Campus, SUM(Case WHEN B.Descrip = 'Ready to Review'
THEN 1 Else 0 END) AS NumberAccts,
MAX(DateDiff(day,C.datelstmod,GetDate())) AS NumberDaysOutstanding,
NumberDaysOutstanding/NumberAccts AS Avg
FROM D JOIN A ON D.SyCampusID = A.SyCampusID
JOIN E ON D.AdEnrollID = E.AdEnrollID
JOIN B ON B.FaPackStatusID = E.FaPackStatusID
JOIN C ON C.RecordID = E.FaStudentAyID
Group BY A.Descrip

hamdanian
Starting Member

4 Posts

Posted - 2010-06-24 : 16:37:47

I also want to add a total Row at the bottom that totals all the values in C1, C2, and C3 for each column. The report will look like this:

Campus Numberaccts NumberDaysOutstanding AvgDays
C1
C2
C3

Total

Does anyone know how to do that?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-25 : 02:31:11

Try this:

WITH cte AS (
	Select 
		SyCampus.Descrip as Campus, 
		SUM(Case WHEN B.Descrip = 'Ready to Review' THEN 1 Else 0 END) AS NumberAccts, 
		MAX(DateDiff(day,C.datelstmod,GetDate())) AS NumberDaysOutstanding,
		
	FROM D 
		JOIN A ON D.SyCampusID = A.SyCampusID 
		JOIN E ON D.AdEnrollID = E.AdEnrollID 
		JOIN B ON B.FaPackStatusID = E.FaPackStatusID
		JOIN C ON C.RecordID = E.FaStudentAyID 
	Group BY A.Descrip
	)
SELECT 
	*,
	NumberDaysOutstanding/NumberAccts AS [Avg]
FROM cte

The total at the bottom should be calculated in your presentation layer (where you create the actual report layout).

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-25 : 02:32:07

And next time you post TSQL please add [ code ] tags around it...it preserves the formatting and makes it alot easier to read.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

hamdanian
Starting Member

4 Posts

Posted - 2010-06-25 : 18:15:59

I should have mentioned it earlier, but I'm creating a stored procedure and what Lumbago gave me did not work in the stored procedure. This is the error message I received: Incorrect syntax near 'cte'.

However, I ran your query without the stored procedure and I got this error message: Divide by zero error encountered. Lumbago I don't know if you could answer both questions, hopefully you can... you seem knowledgeable.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-28 : 03:10:02

Some small ajustments then:

CREATE PROCEDURE dbo.ProcName
AS

WITH cte AS (
	Select 
		SyCampus.Descrip as Campus, 
		SUM(Case WHEN B.Descrip = 'Ready to Review' THEN 1 Else 0 END) AS NumberAccts, 
		MAX(DateDiff(day,C.datelstmod,GetDate())) AS NumberDaysOutstanding
	FROM D 
		JOIN A ON D.SyCampusID = A.SyCampusID 
		JOIN E ON D.AdEnrollID = E.AdEnrollID 
		JOIN B ON B.FaPackStatusID = E.FaPackStatusID
		JOIN C ON C.RecordID = E.FaStudentAyID 
	Group BY A.Descrip
	)
SELECT 
	*,
	[Avg] = CASE WHEN NumberAccts = 0 THEN 0 ELSE NumberDaysOutstanding/NumberAccts END
FROM cte

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources