Sql-server – SQL query to list software by major versions

sql server

I have the following code i am tiring to get it to only show one line in the results for each major version, i need to combine all of the rows that are the same Like so.

SELECT
COUNT(arp.displayname) AS 'Count'
,arp.displayname
,swc.MajorVersion
,arp.Publisher

FROM
Inv_AddRemoveProgram arp
INNER JOIN
vComputer vc
ON arp.[_ResourceGuid] = vc.[Guid]
inner join
[Inv_Software_Component] swc on arp._SoftwareComponentGuid = swc._ResourceGuid


WHERE
arp.DisplayName NOT IN
(
'Power Scheme Plug-in Setup'
,'Altiris Inventory Agent'
,'Patch Management Agent'
,'Deployment Solution Agent'
,'Software Management Solution Agent'
,'Altiris Application Metering Agent'
,'Symantec pcAnywhere'
,'Symantec_pcAnywhere_plugin_installer'
,'Software Management Solution Plugin'
)
AND
arp.DisplayName NOT LIKE 'Security Update%'
AND
arp.DisplayName NOT LIKE 'Update for%'
AND
arp.DisplayName NOT LIKE 'Hotfix for%'
AND
arp.DisplayName NOT LIKE '%SQL_PRODUCT_SHORT%'
GROUP BY
arp.DisplayName, arp.Publisher, swc.MajorVersion

order by
arp.DisplayName asc

Results :

Count   displayname MajorVersion

41  Adobe Reader 8  8

1   Adobe Reader 8.1.1  8

40  Adobe Reader 8.1.3  8

1   Adobe Reader 9.3    9

1   Adobe Reader 9.3.3  9

1   Adobe Reader 9.4.0  9

1   Adobe Reader 9.5.5  9

4   Adobe Reader X (10.1.13)    10

1   Adobe Reader X (10.1.4) 10

1   Adobe Reader X MUI  10

7   Adobe Reader XI 11

139 Adobe Reader XI (11.0.02)   11

1   Adobe Reader XI (11.0.03)   11

2   Adobe Reader XI (11.0.06)   11

28  Adobe Reader XI (11.0.07)   11

1   Adobe Reader XI (11.0.08)   11

62  Adobe Reader XI (11.0.09)   11

5527    Adobe Reader XI (11.0.10)   11

3   Adobe Reader XI (11.0.10)  MUI  11

Best Answer

try this: I removed a few fields and changed the group by

SELECT
COUNT(arp.displayname) AS 'Count'
,arp.displayname
-- remove these fields
--,swc.MajorVersion
--,arp.Publisher

FROM
Inv_AddRemoveProgram arp
INNER JOIN
vComputer vc
ON arp.[_ResourceGuid] = vc.[Guid]
inner join
[Inv_Software_Component] swc on arp._SoftwareComponentGuid = swc._ResourceGuid


WHERE
arp.DisplayName NOT IN
(
'Power Scheme Plug-in Setup'
,'Altiris Inventory Agent'
,'Patch Management Agent'
,'Deployment Solution Agent'
,'Software Management Solution Agent'
,'Altiris Application Metering Agent'
,'Symantec pcAnywhere'
,'Symantec_pcAnywhere_plugin_installer'
,'Software Management Solution Plugin'
)
AND
arp.DisplayName NOT LIKE 'Security Update%'
AND
arp.DisplayName NOT LIKE 'Update for%'
AND
arp.DisplayName NOT LIKE 'Hotfix for%'
AND
arp.DisplayName NOT LIKE '%SQL_PRODUCT_SHORT%'
--change the group by
--GROUP BY
--arp.DisplayName, arp.Publisher, swc.MajorVersion

-- to this
group by arp.DisplayName


order by
arp.DisplayName asc
Related Question