jebba,
No doubt, it can be a bit daunting when you start GROUPing BY and COUNTing (count still gets me sometimes!)  The trick with using "aggregate" functions (like COUNT, MAX, MIN, etc) is that you then have to GROUP BY all the other columsn which aren't the column being counted.  The following should work (note I don't have access to my NS at the moment, so the column names may be a bit off).  Also this is designed to have a "Collection Picker" parameter included; if you don't want the collection picker option, just comment out the JOIN and the rest that references cm.*:
 

SELECT arp.Name, arp.Publisher, arp.Version, count(*) AS [Total]
FROM Inv_AeX_OS_Add_Remove_Programs arp
JOIN CollectionMembership cm
ON arp._ResourceGuid = cm.ResourceGuid
AND cm.CollectionGuid = '%_Collection%'
GROUP BY arp.Name, arp.Publisher, arp.Version
ORDER BY COUNT(*) DESC  -- you can also use numbers here, 1 corresponds to the first column, 2 to the second, etc.

Jebba,

You'll see that if you don't have a _Collection parameter defined referring to the Collection picker.

Drop lines 3, 4 and 5 from Kyle's query if you don't want to filter by collection (e.g. target "All Computers" always) and it should work for you without having to add the parameter.

If a forum post solves your problem, please flag it as a solution. If you like an article, blog post or download vote it up.

In my environment I almost always have to filter reports by collections, so I always put in the Collection Picker bit to help filter down the results.  The only downside is that if you want to set a default Collection, you have to export the report to XML, edit in the collection GUID you want as the default, then re-import it to the console.  Not exactly user-friendly.

I will try to post up (as a download) my customized Add/Remove Programs report which is based on the above.  It has drilldowns to detailed view as well.