Select Top 20 Percent Software Installed - Query Result shows software that dont belong to top 20 percent

Question

I am hoping to create a report using Report Builder 3.0 that will show the Top 20 percent of software installed on the company PC's where those PC's have SCCM Client installed. I used the Select top 20 percent query below but the result shows software that has a count of 1.

Note that there are around 15,000 PC's i may be wrong on this but i would expect that those software with 1 count should not be included on the report?

Appreciate if anyone could guide me please as I may have used the wrong syntax?

Thanks in advance.

SELECT TOP 20 percent 
    s.DisplayName0, s.Publisher0
FROM 
    v_Installed_Software_Categorized as s

Answer 1

I think you want something like this:

SELECT TOP 20 PERCENT 
       DisplayName0, Publisher0, COUNT(*)
FROM v_Installed_Software_Categorized
GROUP BY DisplayName0, Publisher0
ORDER BY COUNT(*) DESC