Video Screencast Help

report sql

Created: 25 Feb 2013 • Updated: 13 Mar 2013 | 1 comment
This issue has been solved. See solution.

 

 

 

 

 

 

 

This code shows me the Microsoft software installed on a computer, also tell me how many are installed.

example:

    software                       version                   guid                             total

microsoft office 2003        1.3.65.54.         as4a-sdf87e5-8fsd4f               250

 

the problem is, that if there is an office 2003 in Spanish and one in English, repeats and counted it as another

 

example:

 

software                                         version                     guid                             total

microsoft office 2003 (español)       1.3.65.54.         as4a-sdf87e5-8fsd4f               15

microsoft office 2003  (ingles)         1.3.65.54.         as4a-sdf87e5-8fsd4f               8

microsoft office 2003                      1.3.65.54.         as4a-sdf87e5-8fsd4f               8

--------------------------prevent count as separate.  I put them together regardless of language

 

 

 

 

 

SELECT DISTINCT
                      dca5_AddRemoveProgram.DisplayName AS Software,
                      dca5_AddRemoveProgram._SoftwareComponentGuid AS GUID,
                      dca5_AddRemoveProgram.DisplayVersion AS Version,
                      dca5_AddRemoveProgram.Publisher AS Compañia,

      COUNT(dca5_AddRemoveProgram.DisplayName) AS Total
FROM        
      Inv_AddRemoveProgram AS dca5_AddRemoveProgram

INNER JOIN
      vComputer AS vComputer ON vComputer.Guid = dca5_AddRemoveProgram._ResourceGuid
WHERE    
      dca5_AddRemoveProgram.DisplayName LIKE 'microsoft%' AND --dca5_AddRemoveProgram.DisplayName LIKE 'microsoft%' AND
         ((dca5_AddRemoveProgram.DisplayName NOT LIKE '%web%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%MUI%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%primary%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%security%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%runtime%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%activation%') AND 
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%update%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%live%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%components%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%viewer%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%programs%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%connector%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%service pack%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%trial%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%labs%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%plugin%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%proof%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%library%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%evaluation%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%sample%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%templante%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%lenguage%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%engine%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%add-in%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%inside%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%resource kit%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%chinese%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%Viewer%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%Library%') AND
(dca5_AddRemoveProgram.InstallFlag LIKE '1'))

GROUP BY dca5_AddRemoveProgram.DisplayName,
         dca5_AddRemoveProgram.DisplayVersion,
         dca5_AddRemoveProgram.Publisher,
         dca5_AddRemoveProgram._SoftwareComponentGuid