Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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...
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SergioM
Posting Yak Master

170 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

549 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

170 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

549 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

170 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  
 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.09 seconds. Powered By: Snitz Forums 2000