(RESOLVED) GROUP BY & HAVING clause are causing...

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

(RESOLVED) GROUP BY & HAVING clause are causing...

Author

Topic

SergioM
Posting Yak Master

170 Posts

Posted - 2013-09-12 : 12:14:07

I have to find duplicate occurrences of StreetLine1 across 2 days & show the count of duplicates. It's easy enough, but SQL server is throwing an error (below). It looks just like the tutorial. It's remedial, but I can't get it to work. Any help is appreciated.

quote:
Msg 8120, Level 16, State 1, Line 2
Column 'SC.dbo.bvc_Order.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

SELECT TOP 10
	   o.[ID]
      ,o.[TimeOfOrder]
      ,x.[StreeLine1]
	  ,DATEDIFF(HOUR,o.[TimeOfOrder],(DATEADD(Hour,  -48, GETDATE())))
FROM [SC].[dbo].[bvc_Order] o
  FULL JOIN SC.dbo.XMLADDRESSREAD x
  ON o.ID= x.id	
WHERE DATEDIFF(HOUR,o.[TimeOfOrder],(DATEADD(Hour,  -48, GETDATE()))) < 48
GROUP BY x.[StreeLine1]
HAVING COUNT(x.[StreeLine1])>1

-Sergio
I use Microsoft SQL 2008

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-09-12 : 12:26:16

Try this:
[CODE]
; WITH CTE AS
(SELECT o.[ID]
,o.[TimeOfOrder]
,x.[StreeLine1]
,DATEDIFF(HOUR,o.[TimeOfOrder],(DATEADD(Hour, -48, GETDATE())))
, ROW_NUMBER() OVER(PARTITION BY x.[StreeLine1] ORDER BY o.ID) as RN
FROM [SC].[dbo].[bvc_Order] o
FULL JOIN SC.dbo.XMLADDRESSREAD x
ON o.ID= x.id )
SELECT * FROM CTE WHERE RN > 1;
[/CODE]

SergioM
Posting Yak Master

170 Posts

Posted - 2013-09-12 : 12:32:52

Sorry, rookie mistake. I added to the GROUP by Clause & it doesn't crash

GROUP BY x.[StreeLine1], o.ID, o.TimeOfOrder

I also tried your code, but it threw the following error:

quote:
Msg 8155, Level 16, State 2, Line 1
No column name was specified for column 4 of 'CTE'.

-Sergio
I use Microsoft SQL 2008

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-09-12 : 12:39:43

quote:
Originally posted by MuMu88

Try this:
[CODE]
; WITH CTE AS
(SELECT o.[ID]
,o.[TimeOfOrder]
,x.[StreeLine1]
,DATEDIFF(HOUR,o.[TimeOfOrder],(DATEADD(Hour, -48, GETDATE()))) as TimeDifference , ROW_NUMBER() OVER(PARTITION BY x.[StreeLine1] ORDER BY o.ID) as RN
FROM [SC].[dbo].[bvc_Order] o
FULL JOIN SC.dbo.XMLADDRESSREAD x
ON o.ID= x.id )
SELECT * FROM CTE WHERE RN > 1;
[/CODE]

SergioM
Posting Yak Master

170 Posts

Posted - 2013-09-12 : 16:29:19

quote:
Originally posted by MuMu88

quote:
Originally posted by MuMu88

Try this:
[CODE]
; WITH CTE AS
(SELECT o.[ID]
,o.[TimeOfOrder]
,x.[StreeLine1]
,DATEDIFF(HOUR,o.[TimeOfOrder],(DATEADD(Hour, -48, GETDATE()))) as TimeDifference , ROW_NUMBER() OVER(PARTITION BY x.[StreeLine1] ORDER BY o.ID) as RN
FROM [SC].[dbo].[bvc_Order] o
FULL JOIN SC.dbo.XMLADDRESSREAD x
ON o.ID= x.id )
SELECT * FROM CTE WHERE RN > 1;
[/CODE]

Works nicely, thanks!

-Sergio
I use Microsoft SQL 2008

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources