Select statement problem, help needed.

[bbates]

Table: Software

SoftwareID
SoftwareName
Version
ProductKey

Alright what I am doing is figuring out the total number of installations of software in the business, and the total number of licenses. I figured out how to do that with this:

SELECT Count(Software.ProductKey) AS [Total Installations], (SELECT Count(*)
FROM (SELECT DISTINCT Software.ProductKey FROM Software WHERE Software.Version='2000' AND Software.SoftwareName='Microsoft Office'
GROUP BY Software.ProductKey) AS temp1) AS [Unique Product Keys]
FROM Software
WHERE (((Software.Version)='2000') AND ((Software.SoftwareName)='Microsoft Office'));

That returns the correct numbers. But I would like another query that returns two columns like the one above: Total Installations and Unique Product Keys. However instead of doing a COUNT I would like to list all of the product keys. So far I haven't been able to figure it out.

 

[rac2]

The number of times each copy was installed would be

SELECT Software.ProductKey,
       Count(*) AS [Total Installations]
FROM Software
WHERE Software.Version='2000' AND Software.SoftwareName='Microsoft Office' 
GROUP BY Software.ProductKey

But that is too easy. I think I don't understand your question.

 

[dnoeth]

SELECT
Count(Software.ProductKey) AS "Total Installations",
Count(DISTINCT Software.ProductKey*) AS "Unique Product Keys"
FROM Software
WHERE Software.Version='2000' AND Software.SoftwareName='Microsoft Office'

Dieter

 

[bbates]

Thanks for trying guys, but I wanted the actual Product Keys to print out, hence the last line of my question.. "However instead of doing a COUNT I would like to list all of the product keys." Example:

Total Installations Unique Product Keys
11111111 11111111
22222222 22222222
11111111 33333333
33333333

Notice the second "111111111" doesn't show up in Unique Product Keys.. thanks for trying though. It doesn't matter anymore, I don't need it.