Count same column 2 different conditions, 1 recordset 1

[Fingerprint]

Hi All,

I'm working with a database of musical records. There's a table of artists, and another of tracks by that artist. Each track has a genre, where '1' is unclassfied, and anything else is a foreign key to the table of genres.

What I'm trying to do is produce a single html table which has the artist name, the number of unclassified tracks they have, and the number of other tracks by them in the system.

I have put together a query which will count either the unclassified or the classified tracks, but can't work out how to get one query to do both.

SELECT DISTINCT Artist.name, COUNT (Tracks.trackName) as howmany
FROM Artist, Tracks
WHERE Artist.artistRef = Tracks.artistRef 
AND Tracks.genreRef = 1
AND Artist.name LIKE '[A]%'
GROUP BY Artist.name
HAVING COUNT(Tracks.trackName) > 5
ORDER BY COUNT(Tracks.trackName) DESC;

I can't change the database structure as this is an existing system. If it's not possible, I just need to know so I can start bodging a work around in asp.

Thanks.



"The secret to creativity is knowing how to hide your sources" - Einstein

 

[HarleyQuinn]

How about using an IIF statement?

Something like:

SELECT DISTINCT Artist.Name, IIf([Tracks.GenreRef]=1,"Unclassified","Classified") AS Classification, Count(Tracks.TrackName) AS Track_Count
FROM Artist, Tracks
WHERE Artist.artistRef = Tracks.ArtistRef 
GROUP BY Artist.name IIf([Genre]="1","Unclassified","Classified")
ORDER BY COUNT(Tracks.trackName) DESC;

That could be easily converted to have a column for each of the criteria (classified/unclassified) if that is what you prefer.

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

Oops! I missed a comma after Artist.name in the GROUP BY

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Fingerprint]

Thanks for the advice!

I've never come across one of those statements before (being a web developer, I only have basic SQL knowledge). What type of output does it give me?

"The secret to creativity is knowing how to hide your sources" - Einstein

 

[HarleyQuinn]

Basically the query as it stands would return something like:

Name Classification Track_Count

<ARTIST NAME> <CLASSIFIED OR UNCLASSIFED> <COUNT OF TRACKS>

If that's not a great format for you it can be modified to display the data as:

Name CountofClassifed CountofUnclassified

Did you try the query to see what the output was?

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Fingerprint]

Just after I posted again I thought 'Duh! Just try it'. Bad brain day : )

It works perfectly, but I do need to modify it into the layout you mention - so if you can tell me how, I'll be forever grateful!

Thanks again,

&quot;The secret to creativity is knowing how to hide your sources&quot; - Einstein

 

[Fingerprint]

Lots of thought later - my solution is:

SELECT Artist.name,
SUM(IIF(Tracks.genreRef = 1,1,0)) AS unclassifiedCount,
SUM(IIF(Tracks.genreRef <> 1,1,0) AS classifedCount
FROM
 (SELECT DISTINCT Artist.name, Tracks.trackName, Tracks.genreRef 
  FROM Artist, Tracks
  WHERE Artist.artistRef = Tracks.artistRef)
GROUP BY Artist.name
HAVING COUNT(Tracks.trackName) > 5
ORDER BY SUM(IIF(Tracks.genreRef = 1,1,0)) DESC, Artist.name;

There's a subquery because Access was giving me problems when it tried to do the 'distinct' at the same time.

Thanks to Harley Quinn for putting me on the right track. Hope it helps some other guys out there too.



&quot;The secret to creativity is knowing how to hide your sources&quot; - Einstein