Select SQL with Max() not working...help!!

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

Select SQL with Max() not working...help!!

Author

Topic

cdipen
Starting Member

3 Posts

Posted - 2009-06-30 : 10:44:49

Hi, Guys

I'm kinda new to SQL Server but have worked with Oracle for few years and know SQL quite well but need help with this...

I got this SQL working gr8

SELECT audsvc, COUNT(*)AS cnt, DATEPART(Hh, audtime) as Hr, CONVERT(VARCHAR(10), auddate, 101) AS Det
FROM iBank.dbo.audits audits
WHERE (auddate BETWEEN '6/28/2009 12:00:00 AM' AND '6/29/2009 12:00:00 AM'
AND audtxt = 'Service Selected.')
GROUP BY audsvc, DATEPART(Hh, audtime), CONVERT(VARCHAR(10), auddate, 101)

RESULTS

audsvc cnt Hr Det
------------ ----------- ----------- ----------
MTIn 767 11 06/29/2009
PPManualIssu 43 3 06/29/2009
BRRept 962 13 06/29/2009
PPExceptnRpt 19 1 06/29/2009
Irrept 72 22 06/29/2009
PPCheckRetrn 71 5 06/29/2009
MTIn 761 22 06/28/2009
PosPayStatus 67 13 06/29/2009
Irrept 2189 2 06/29/2009
SPReport 232 14 06/29/2009
PPManualVoid 52 3 06/29/2009
SPCheckInq 266 5 06/29/2009
MtRpts 101 1 06/29/2009
PPManualIssu 7 6 06/29/2009

But when I try the following SQL that bring back MAX for cnt and group by audsvc I cant seem to get it to work. I getting error... incorrect syntax near the keyword 'GROUP'. Maybe i'm not using MAX funtion the right way, but similar SQL in Oracle works just fine.

SELECT audsvc, MAX(cnt) as MAX_HOUR
FROM
(SELECT audsvc, audtxt, COUNT(*) AS cnt, DATEPART(Hh, audtime) as Hr, CONVERT(VARCHAR(10), auddate, 101) AS Det
FROM iBank.dbo.audits audits
WHERE (auddate BETWEEN '6/28/2009 12:00:00 AM' AND '6/29/2009 12:00:00 AM'
AND audtxt = 'Service Selected.')
GROUP BY audsvc, DATEPART(Hh, audtime), CONVERT(VARCHAR(10), auddate, 101))
GROUP BY audsvc
ORDER BY audsvc

Any help to get the above SQL working would be wonderful

Thanks!!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-06-30 : 10:49:47

A table alias is missing...Try this

SELECT A.audsvc, MAX(A.cnt) as MAX_HOUR
FROM
(SELECT audsvc, audtxt, COUNT(*) AS cnt, DATEPART(Hh, audtime) as Hr, CONVERT(VARCHAR(10), auddate, 101) AS Det
FROM iBank.dbo.audits audits
WHERE (auddate BETWEEN '6/28/2009 12:00:00 AM' AND '6/29/2009 12:00:00 AM'
AND audtxt = 'Service Selected.')
GROUP BY audsvc, DATEPART(Hh, audtime), CONVERT(VARCHAR(10), auddate, 101)) A
GROUP BY A.audsvc
ORDER BY A.audsvc

X002548
Not Just a Number

15586 Posts

Posted - 2009-06-30 : 10:53:16

audtxt needs to be part of the grouping in the derived table, and you need to label the derived table..as mention


SELECT audsvc, MAX(cnt) as MAX_HOUR
  FROM (SELECT  audsvc
			 ,  audtxt
			 ,  DATEPART(Hh, audtime) as Hr
			 ,  CONVERT(VARCHAR(10), auddate, 101) AS Det
			 ,  COUNT(*) AS cnt
		  FROM  iBank.dbo.audits audits
		 WHERE  auddate BETWEEN '6/28/2009 12:00:00 AM' AND '6/29/2009 12:00:00 AM'
		   AND  audtxt = 'Service Selected.'
	   GROUP BY  audsvc
			  , audtxt
			  , DATEPART(Hh, audtime)
			  , CONVERT(VARCHAR(10), auddate, 101)
		) AS t
GROUP BY audsvc
ORDER BY audsvc

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

cdipen
Starting Member

3 Posts

Posted - 2009-06-30 : 10:59:08

Thanks for the quick reply VJ

I gave that a try but got this error back.
Column 'audits.audtxt' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

cdipen
Starting Member

3 Posts

Posted - 2009-06-30 : 11:08:27

Brett... you're the man, Thanks so much!!

working like a champ now. :)

SELECT audsvc, MAX(cnt) as MAX_HOUR
FROM (SELECT audsvc, audtxt, DATEPART(Hh, audtime) as Hr, CONVERT(VARCHAR(10), auddate, 101) AS Det, COUNT(*) AS cnt
FROM iBank.dbo.audits audits
WHERE auddate BETWEEN '6/28/2009 12:00:00 AM' AND '6/29/2009 12:00:00 AM'
AND audtxt = 'Service Selected.'
GROUP BY audsvc, audtxt, DATEPART(Hh, audtime), CONVERT(VARCHAR(10), auddate, 101)) AS t
GROUP BY audsvc
ORDER BY audsvc

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-30 : 14:23:00

Simplified

SELECT		audsvc,
		MAX(cnt) AS MAX_HOUR
FROM		(
			SELECT		audsvc,
					COUNT(*) AS cnt
			FROM		iBank.dbo.audits 
			WHERE		auddate >= '20090628'
					AND auddate < '20090629'
					AND audtxt = 'Service Selected.'
			GROUP BY	audsvc,
					DATEPART(HOUR, audtime)
		) AS t
GROUP BY	audsvc
ORDER BY	audsvc

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

X002548
Not Just a Number

15586 Posts

Posted - 2009-06-30 : 15:29:30

AH....but I'm guessing they might want to know which hour and on what day....what the text is for? anyone's guess

which is a different problem

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources