Distinct query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Distinct query

Author

Topic

alexmarshuk
Starting Member

12 Posts

Posted - 2010-02-04 : 08:56:52

Hi Guys, I have the sql below...

SELECT DisplayName0, publisher0, Count(*) AS 'Count'

FROM v_Simon_Software_list arp

JOIN v_FullCollectionMembership fcm on arp.ResourceID=fcm.ResourceID

WHERE fcm.CollectionID = 'CAR000DB' and displayname0 = 'adobe flash player 10 activex'

GROUP BY DisplayName0,publisher0

ORDER BY DisplayName0

This returns two lines as shown below:

Adobe Flash Player 10 ActiveX Adobe Systems Incorporated 119
Adobe Flash Player 10 ActiveX Adobe Systems, Inc. 837

I need this to be only one line, ie showing adobe flash playeer 10 active x with the totals added together and showeing adobe systems incorporated.

how do I do this? I can only get it to show 2 lines as the pubisher0 are different. ta

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 09:00:13

quote:
Originally posted by alexmarshuk

Hi Guys, I have the sql below...

SELECT DisplayName0, ~~,publisher0~~, Count(*) AS 'Count'

FROM v_Simon_Software_list arp

JOIN v_FullCollectionMembership fcm on arp.ResourceID=fcm.ResourceID

WHERE fcm.CollectionID = 'CAR000DB' and displayname0 = 'adobe flash player 10 activex'

GROUP BY DisplayName0~~,publisher0~~

ORDER BY DisplayName0

This returns two lines as shown below:

Adobe Flash Player 10 ActiveX Adobe Systems Incorporated 119
Adobe Flash Player 10 ActiveX Adobe Systems, Inc. 837

I need this to be only one line, ie showing adobe flash playeer 10 active x with the totals added together and showeing adobe systems incorporated.

how do I do this? I can only get it to show 2 lines as the pubisher0 are different. ta

modify like above

alexmarshuk
Starting Member

12 Posts

Posted - 2010-02-04 : 09:03:02

I will need the report to show the publisher0 field tho. the problem with this report is abode using two different names. i dont mind which one appears on the reports. an almagamation would be fine.thank!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 09:07:23

then do like this

SELECT DisplayName0, ,MAX(publisher0), Count(*) AS 'Count'

FROM v_Simon_Software_list arp

JOIN v_FullCollectionMembership fcm on arp.ResourceID=fcm.ResourceID 

WHERE fcm.CollectionID = 'CAR000DB' and displayname0 = 'adobe flash player 10 activex'

GROUP BY DisplayName0

ORDER BY DisplayName0

alexmarshuk
Starting Member

12 Posts

Posted - 2010-02-04 : 09:11:57

thanks!! excellent stuff

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 09:14:52

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources