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.
| Author |
Topic |
|
mqh7
Yak Posting Veteran
58 Posts |
 Posted - 2012-09-17 : 12:01:39
|
| If I run all the code above the dotted line it works. It will return a list of computers and their serial numbers. But if I run all of the code I get this error.Msg 8120, Level 16, State 1, Line 2Column 'v_GS_COMPUTER_SYSTEM.Name0' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.How would adding the GROUP BY and HAVING lines to my code cause this error? SELECT cs.Name0 AS [Computer Name],rs.User_Name0 AS [User Name],rs.User_Domain0 AS [Domain],gs.SerialNumber0 AS [Serial Number]FROM v_GS_COMPUTER_SYSTEM cs JOIN v_R_System rs ON cs.resourceID = rs.resourceIDJOIN v_GS_PC_BIOS gs ON cs.ResourceID = gs.ResourceID-----------------------------------GROUP BY gs.SerialNumber0 HAVING COUNT (cs.Name0) > 1 |
|
|
Abu-Dina
Posting Yak Master
206 Posts |
Posted - 2012-09-17 : 12:26:07
|
quote:
Originally posted by mqh7
If I run all the code above the dotted line it works. It will return a list of computers and their serial numbers. But if I run all of the code I get this error.Msg 8120, Level 16, State 1, Line 2Column 'v_GS_COMPUTER_SYSTEM.Name0' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.How would adding the GROUP BY and HAVING lines to my code cause this error? SELECT cs.Name0 AS [Computer Name],rs.User_Name0 AS [User Name], rs.User_Domain0 AS [Domain],gs.SerialNumber0 AS [Serial Number]FROM v_GS_COMPUTER_SYSTEM cs JOIN v_R_System rs ON cs.resourceID = rs.resourceIDJOIN v_GS_PC_BIOS gs ON cs.ResourceID = gs.ResourceID-----------------------------------GROUP BY gs.SerialNumber0 HAVING COUNT (cs.Name0) > 1
You need some aggregate function wrapped around the fields highlighted in green in your select statement. |
 |
|
|
mqh7
Yak Posting Veteran
58 Posts |
Posted - 2012-09-17 : 12:56:18
|
| "You need some aggregate function wrapped around the fields highlighted in green in your select statement."Can you provide more detail? I am pretty new to SQL so I am not sure how to solve this. |
|
|
|
mqh7
Yak Posting Veteran
58 Posts |
Posted - 2012-09-17 : 14:09:32
|
| I tried this:declare@name0 varchar (99),@serialnumber0 varchar (99)SET @name0 = ''SET @serialnumber0 = ''SELECT cs.Name0 AS [Computer Name],rs.User_Name0 AS [User Name],rs.User_Domain0 AS [Domain],gs.SerialNumber0 AS [Serial Number]FROM v_GS_COMPUTER_SYSTEM cs JOIN v_R_System rs ON cs.resourceID = rs.resourceIDJOIN v_GS_PC_BIOS gs ON cs.ResourceID = gs.ResourceIDGROUP BY gs.SerialNumber0 HAVING (COUNT (cs.Name0) > 1)and I get the same error. |
|
|
|
mqh7
Yak Posting Veteran
58 Posts |
Posted - 2012-09-17 : 14:33:52
|
| OK, I found the issue. I need to add all of my SELECT stuff to the GROUP BY sectionSELECT cs.Name0 AS [Computer Name],rs.User_Name0 AS [User Name],rs.User_Domain0 AS [Domain],gs.SerialNumber0 AS [Serial Number]FROM v_GS_COMPUTER_SYSTEM cs JOIN v_R_System rs ON cs.resourceID = rs.resourceIDJOIN v_GS_PC_BIOS gs ON cs.ResourceID = gs.ResourceID-----------------------------------GROUP BY cs.Name0,rs.User_Name0,rs.User_Domain0,gs.SerialNumber0 HAVING COUNT (cs.Name0) > 1But this also does not return any results. Someone here at work said I need a sub-query if I want to see all of the computers with duplicate serial numbers. how/where do I add the sub-query? |
|
|
|
|
|
|
|
|
Using the Count function