SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 (RESOLVED) GROUP BY & HAVING clause are causing...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SergioM
Posting Yak Master

163 Posts

Posted - 09/12/2013 :  12:14:07  Show Profile  Reply with Quote
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

Edited by - SergioM on 09/12/2013 16:29:44

MuMu88
Aged Yak Warrior

547 Posts

Posted - 09/12/2013 :  12:26:16  Show Profile  Reply with Quote
Try this:

; 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;
Go to Top of Page

SergioM
Posting Yak Master

163 Posts

Posted - 09/12/2013 :  12:32:52  Show Profile  Reply with Quote
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
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 09/12/2013 :  12:39:43  Show Profile  Reply with Quote
quote:
Originally posted by MuMu88

Try this:

; 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;


Go to Top of Page

SergioM
Posting Yak Master

163 Posts

Posted - 09/12/2013 :  16:29:19  Show Profile  Reply with Quote
quote:
Originally posted by MuMu88

quote:
Originally posted by MuMu88

Try this:

; 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;





Works nicely, thanks!

-Sergio
I use Microsoft SQL 2008
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000