happyappys
asked on
Count unique max versions
Hi, the SQL statement below reports two lines from our SMS database as shown below
Microsoft Visio 10.0 96
Microsoft Visio 11.0 56
However, I know that out of the 96 V10.0 installations, 9 of them have been upgraded to V11 so the actual figure should be 87. The database however lists two entries for these upgraded machines, a line for V10 and a line for V11 so it's basically counting these 9 twice using the statement below/ above results.
My question is - is there any way I can now modify the statement further to reduce the figure to 87 - basically asking it to take into account that it has been upgraded to V11 so is counted there, so not to count it twice! Hope that makes sense!! Many thanks in advance, Deborah.
Microsoft Visio 10.0 96
Microsoft Visio 11.0 56
However, I know that out of the 96 V10.0 installations, 9 of them have been upgraded to V11 so the actual figure should be 87. The database however lists two entries for these upgraded machines, a line for V10 and a line for V11 so it's basically counting these 9 twice using the statement below/ above results.
My question is - is there any way I can now modify the statement further to reduce the figure to 87 - basically asking it to take into account that it has been upgraded to V11 so is counted there, so not to count it twice! Hope that makes sense!! Many thanks in advance, Deborah.
SELECT TOP (100) PERCENT dbo.vSMS_G_System_SoftwareProduct.ProductName, MAX(DISTINCT dbo.vSMS_G_System_SoftwareProduct.ProductVersion)
AS Expr1, COUNT(DISTINCT dbo.vSMS_G_System_SoftwareProduct.ClientId) AS Expr2, dbo.vSMS_G_System_SoftwareProduct.ProductVersion
FROM dbo.vSMS_G_System_SoftwareProduct INNER JOIN
dbo.v_R_System ON dbo.vSMS_G_System_SoftwareProduct.ClientId = dbo.v_R_System.ResourceID
GROUP BY dbo.vSMS_G_System_SoftwareProduct.ProductName, dbo.vSMS_G_System_SoftwareProduct.ProductVersion
HAVING (dbo.vSMS_G_System_SoftwareProduct.ProductName = 'microsoft visio')
ORDER BY Expr2 DESC
ASKER
Hi, the Top(10) percent was put in by sql server 2005 automatically, it does it every time. There is no installed date or upgraded date, sms just trawles round PC's looking at what sofware is installed and basically makes a list of it. I have checked on one of the users PC's the only reason I can assume that there's two entries in the database is because when Microsoft upgrades visio it leaves some files of the old version behind which SMS is registering. Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It looks like AntonyDN's query works as well...just beat me to it. :-)
The one thing I added to mine is that I return the number of clients for ALL product versions, whether any clients have that version or not. Not even sure if you will need this functionality.
The one thing I added to mine is that I return the number of clients for ALL product versions, whether any clients have that version or not. Not even sure if you will need this functionality.
ASKER
WOW, thanks guys that's amazing!! Just one last question then - if I want this query to show ALL software installed rather than just Visio, do I just take the WHERE clause out?
Yep!
ASKER
Oh, and yes aaronakin - I need to see individual count of versions too for each software product!
Yes ....
I'm not quite sure what you are after there - I think both queries will give number of versions installed.
If you want to see,
Product NumOfVersions
Microsoft Visio, 2
then that's a different query
Can you clarify?
If you want to see,
Product NumOfVersions
Microsoft Visio, 2
then that's a different query
Can you clarify?
ASKER
It's OK, just removing the where clause gives me the answer, a line for each product and version and the count of them, thanks very much that's fab!! Debs
ASKER
Great!!
Do not use TOP (100) PERCENT in any query or view. It achieves nothing but causes plenty of confusion. TOP (100) PERCENT is just an unfortunate side effect of the query/view designer.