• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 457
  • Last Modified:

query with Distinct and count

Hello,

How can I distinct ( v_R_System.Netbios_Name0) in this query without error ?


Select Count(ProdID0) AS 'Count', v_Add_Remove_Programs.DisplayName0, v_Add_Remove_Programs.Publisher0, distinct ( v_R_System.Netbios_Name0)
FROM v_Add_Remove_Programs
JOIN  v_R_System ON v_Add_Remove_Programs.ResourceID = v_R_System.ResourceID
JOIN v_FullCollectionMembership fcm on v_R_System.ResourceID=fcm.ResourceID
WHERE
DisplayName0 not like '%Adobe Flash%' and
DisplayName0 not like '%Adobe%Reader%' and
DisplayName0 not like '%Agent%' and
DisplayName0 not like '%DWG%' and
DisplayName0 not like '%FileZilla%' and
DisplayName0 not like '%ROXIO%' and
DisplayName0 not like '%Java%' and
DisplayName0 not like '%Microsoft Office%' and
DisplayName0 not like '%7-Zip%' and
DisplayName0 not like '%Forefront%' and
DisplayName0 not like '%PDFCreator%' and
DisplayName0 not like '%Framework%' and
DisplayName0 not like '%Informations Personnelles%' and
DisplayName0 not like '%Microsoft Silverlight%' and
DisplayName0 not like '%Java%' and
DisplayName0 not like '%Fingerprint%' and
DisplayName0 not like '%Microsoft Visual%' and
DisplayName0 not like '%HP Quick%' and
DisplayName0 not like '%Microsoft Operations%' and
DisplayName0 not like '%Mise %jour%' and
DisplayName0 not like '%Hotfix%' and
DisplayName0 not like '%Internet Explorer%' and
DisplayName0 not like '%Genuine%' and
DisplayName0 not like '%J2SE%' and
DisplayName0 not like '%MSXML%' and
DisplayName0 not like '%Windows Media%' and
DisplayName0 not like '%Adobe%Player%' and
DisplayName0 not like '%Windows XP%' and
DisplayName0 not like '%Update%' and
DisplayName0 not like '%Broadcom%' and
DisplayName0 not like '%ATI%' and
DisplayName0 not like '%WIMGAPI%' and
DisplayName0 not like '%RDC%' and
DisplayName0 not like '%WebFldrs XP%' and
DisplayName0 not like '%cartes%puce%' and
DisplayName0 not like '%Synaptics Pointing Device Driver%' and
DisplayName0 not like '%.NET Memory Profiler 3.5%' and
DisplayName0 not like '1500%' and
DisplayName0 not like '2400%' and
DisplayName0 not like '3.1.5' and
DisplayName0 not like '32 Bit%' and
DisplayName0 not like 'Correctif%' and
DisplayName0 not like '5300_5400' and
DisplayName0 not like '64 Bit%' and
DisplayName0 not like '6500%' and
DisplayName0 not like '8600%' and
Publisher0 not like '%Hewlett%Packard%' and
Publisher0 not like 'ATI%'

and fcm.CollectionID='SMS00004'
GROUP BY DisplayName0,Publisher0,Netbios_Name0
Order by Count desc

Thanks

bibi
0
bibi92
Asked:
bibi92
Advertise Here
1 Solution
 
Patrick MatthewsCommented:
Select Count(ProdID0) AS 'Count', v_Add_Remove_Programs.DisplayName0, v_Add_Remove_Programs.Publisher0, v_R_System.Netbios_Name0
FROM v_Add_Remove_Programs
JOIN  v_R_System ON v_Add_Remove_Programs.ResourceID = v_R_System.ResourceID
JOIN v_FullCollectionMembership fcm on v_R_System.ResourceID=fcm.ResourceID
WHERE
DisplayName0 not like '%Adobe Flash%' and
DisplayName0 not like '%Adobe%Reader%' and
DisplayName0 not like '%Agent%' and
DisplayName0 not like '%DWG%' and
DisplayName0 not like '%FileZilla%' and
DisplayName0 not like '%ROXIO%' and
DisplayName0 not like '%Java%' and
DisplayName0 not like '%Microsoft Office%' and
DisplayName0 not like '%7-Zip%' and
DisplayName0 not like '%Forefront%' and
DisplayName0 not like '%PDFCreator%' and
DisplayName0 not like '%Framework%' and
DisplayName0 not like '%Informations Personnelles%' and
DisplayName0 not like '%Microsoft Silverlight%' and
DisplayName0 not like '%Java%' and
DisplayName0 not like '%Fingerprint%' and
DisplayName0 not like '%Microsoft Visual%' and
DisplayName0 not like '%HP Quick%' and
DisplayName0 not like '%Microsoft Operations%' and
DisplayName0 not like '%Mise %jour%' and
DisplayName0 not like '%Hotfix%' and
DisplayName0 not like '%Internet Explorer%' and
DisplayName0 not like '%Genuine%' and
DisplayName0 not like '%J2SE%' and
DisplayName0 not like '%MSXML%' and
DisplayName0 not like '%Windows Media%' and
DisplayName0 not like '%Adobe%Player%' and
DisplayName0 not like '%Windows XP%' and
DisplayName0 not like '%Update%' and
DisplayName0 not like '%Broadcom%' and
DisplayName0 not like '%ATI%' and
DisplayName0 not like '%WIMGAPI%' and
DisplayName0 not like '%RDC%' and
DisplayName0 not like '%WebFldrs XP%' and
DisplayName0 not like '%cartes%puce%' and
DisplayName0 not like '%Synaptics Pointing Device Driver%' and
DisplayName0 not like '%.NET Memory Profiler 3.5%' and
DisplayName0 not like '1500%' and
DisplayName0 not like '2400%' and
DisplayName0 not like '3.1.5' and
DisplayName0 not like '32 Bit%' and
DisplayName0 not like 'Correctif%' and
DisplayName0 not like '5300_5400' and
DisplayName0 not like '64 Bit%' and
DisplayName0 not like '6500%' and
DisplayName0 not like '8600%' and
Publisher0 not like '%Hewlett%Packard%' and
Publisher0 not like 'ATI%'

and fcm.CollectionID='SMS00004'
GROUP BY v_Add_Remove_Programs.DisplayName0, v_Add_Remove_Programs.Publisher0, v_R_System.Netbios_Name0
Order by Count(ProdID0) desc
0
 
bibi92Author Commented:
Thanks bibi
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

Download Free Trial
Advertise Here
Tackle projects and never again get stuck behind a technical roadblock.
Join Now