Store procedure problems with grouping

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 2000 Forums

Transact-SQL (2000)

Store procedure problems with grouping

Author

Topic

sun919
Starting Member

22 Posts

Posted - 2006-12-04 : 00:38:13

Hi there ,
I have a question asking regarding using stored procedure. Basically I have the following store procedure (with help from peso, Thanks man) but I have problem with saving it. When i try to save it give me the following error " t.number is invalid in the select list because it is not contain either aggregate function or group by clause" so how do come about and solve problem , please help

below are my store procedure coding :
SELECT t.number AS Month, ISNULL(SUM(CASE WHEN z.CustomerType = 'New' THEN z.cnt ELSE 0 END), 0) AS 'New',
ISNULL(SUM(CASE WHEN z.CustomerType = 'Old' THEN z.cnt ELSE 0 END), 0) AS 'Old'
FROM (SELECT number
FROM master.dbo.spt_values
WHERE (name IS NULL) AND (number BETWEEN 1 AND 12)) AS t LEFT OUTER JOIN
(SELECT CASE WHEN (DATEPART(month, p.register) = DATEPART(month, a.appointmentDate) AND DATEPART(year, p.register) = DATEPART(year,
a.AppointmentDate)) THEN 'New' ELSE 'Old' END AS CustomerType, DATEPART(month, a.AppointmentDate) AS theMonth, 1 AS cnt
FROM Appointment AS a INNER JOIN
Patient AS p ON p.HNID = a.HNID
WHERE (DATEPART(year,a.AppointmentDate) = @year)
GROUP BY p.HN, CASE WHEN (DATEPART(month, p.register) = DATEPART(month, a.appointmentDate) AND DATEPART(year, p.register) = DATEPART(year,
a.AppointmentDate)) THEN 'New' ELSE 'Old' END, DATEPART(month, a.AppointmentDate)) AS z
ON
z.theMonth = t.number

(where @year is int input)

many thanks
sun

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-04 : 00:56:36

Add t.number in GROUP BY clause!

Harsh Athalye
India.
"Nothing is Impossible"

sun919
Starting Member

22 Posts

Posted - 2006-12-04 : 11:46:35

try to that already and it didnt work it said t.number can not be multiple bound

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 11:56:01

Work ok for me...

SELECT		t.number AS [Month],
		SUM(CASE WHEN z.CustomerType = 'New' THEN z.cnt ELSE 0 END) AS 'New', 
		SUM(CASE WHEN z.CustomerType = 'Old' THEN z.cnt ELSE 0 END) AS 'Old'
FROM		(
			SELECT	number
			FROM	master.dbo.spt_values
			WHERE	name IS NULL
				AND number BETWEEN 1 AND 12
		) AS t
LEFT JOIN	(
			SELECT		CASE
						WHEN DATEPART(month, p.register) = DATEPART(month, a.appointmentDate) AND DATEPART(year, p.register) = DATEPART(year, 
a.AppointmentDate) THEN 'New'
						ELSE 'Old'
					END AS CustomerType,
					DATEPART(month, a.AppointmentDate) AS theMonth,
					1 AS cnt
			FROM		Appointment AS a
			INNER JOIN	Patient AS p ON p.HNID = a.HNID
			WHERE		DATEPART(year, a.AppointmentDate) = @year
			GROUP BY	p.HN,
					CASE
						WHEN DATEPART(month, p.register) = DATEPART(month, a.appointmentDate) AND DATEPART(year, p.register) = DATEPART(year, 
a.AppointmentDate) THEN 'New'
						ELSE 'Old' END,
					DATEPART(month, a.AppointmentDate)
		) AS z ON z.theMonth = t.number
GROUP BY	t.number

Peter Larsson
Helsingborg, Sweden

sun919
Starting Member

22 Posts

Posted - 2006-12-04 : 12:16:00

thanks u peter i got the problem solve now

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 13:37:54

It was the last GROUP BY t.number, right?

Peter Larsson
Helsingborg, Sweden

sun919
Starting Member

22 Posts

Posted - 2006-12-05 : 11:11:29

yes

sun919
Starting Member

22 Posts

Posted - 2006-12-06 : 02:40:04

If i want to sort the order from october 2005- sep 2006 how would i do with with above sql command

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-06 : 02:55:36

Sometihng like this. I can't test it since I don't have the source tables

SELECT		z.theYear [Year],
		t.number AS [Month],
		SUM(CASE WHEN z.CustomerType = 'New' THEN z.cnt ELSE 0 END) AS 'New', 
		SUM(CASE WHEN z.CustomerType = 'Old' THEN z.cnt ELSE 0 END) AS 'Old'
FROM		(
			SELECT	number
			FROM	master.dbo.spt_values
			WHERE	name IS NULL
				AND number BETWEEN 1 AND 12
		) AS t
LEFT JOIN	(
			SELECT		CASE
						WHEN DATEPART(month, p.register) = DATEPART(month, a.appointmentDate)
							AND DATEPART(year, p.register) = DATEPART(year, a.AppointmentDate) THEN 'New'
						ELSE 'Old'
					END AS CustomerType,
					DATEPART(year, a.AppointmentDate) AS theYear,
					DATEPART(month, a.AppointmentDate) AS theMonth,
					1 AS cnt
			FROM		Appointment AS a
			INNER JOIN	Patient AS p ON p.HNID = a.HNID
			WHERE		a.AppointmentDate BETWEEN '20051001' AND '20060930'
			GROUP BY	p.HN,
					CASE
						WHEN DATEPART(month, p.register) = DATEPART(month, a.appointmentDate)
							AND DATEPART(year, p.register) = DATEPART(year, a.AppointmentDate) THEN 'New'
						ELSE 'Old' END,
					DATEPART(year, a.AppointmentDate),
					DATEPART(month, a.AppointmentDate)
		) AS z ON z.theMonth = t.number
GROUP BY	z.theYear,
		t.number

Peter Larsson
Helsingborg, Sweden

sun919
Starting Member

22 Posts

Posted - 2006-12-06 : 05:05:19

Thanks u so much peter it works perfectly ....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources