CODEWITHSUNDEEP
mysqlsql
tekos
tekos

Reputation: 67

Select count of multiple values from multiple tables

I have a radio station's database of songs added into reports by individual programs. I would like to select album, album_id, count of songs in an album, and count of album occurrences in reports history of a single artist. So I want the following result:

| album_id | album_name | track_count | report_occurrence_count |
|---------------------------------------------------------------|
| 1        | Name 1     | 10          | 25                      |
| 2        | Name 2     | 15          | 65                      |
| 3        | Name 3     | 23          | 11                      |

EDIT: Relevant tables to select values from:

| album     | artist     | track       | report_track   |
|-------------------------------------------------------|
| id        | id         | id          | id             |
| artist_id | name       | album_id    | report_id      |
| name      | ...        | artist_id   | track_id       |
| ...       | ...        | ...         | ...            |

I can get either the track count or the report occurrences count, but not both. I've tried the following:

SELECT al.id as album_id, al.name, al.identifier, count(tr.album_id) as track_count, 
count(rt.track_id) as report_occurrence
FROM playlist__album as al, playlist__artist as ar, playlist__track as tr,
playlist__report_track as rt
WHERE al.artist_id = ar.id
and tr.album_id = al.id
and rt.track_id = tr.id
and ar.id = 39887
group by album_id

But that gives me the report_occurrence count to both track_count and report_occurrence_count columns. If I remove count(rt.track_id) as report_occurrence, I get the correct value for track_count. I've tried the method found in here but that gives me the count of all tracks by artist, instead of count by album.

DB tables are like so: tables

Upvotes: 2

Views: 115

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

Could be you need distinct track_no for tracks 

SELECT al.id as album_id
 , al.name
 , al.identifier
 , count(distinct tr.track_no) as track_count
 , count(rt.track_id) as report_occurrence
FROM playlist__album as al
INNER JOIN  playlist__artist as ar ON al.artist_id = ar.id
INNER JOIN  playlist__track as tr ON and tr.album_id = al.id
INNER JOIN  playlist__report_track as rt ON  rt.track_id = tr.id
WHERE ar.id = 39887
group by album_id

And you should avoid old implicit join syntax based on where and comma separated list of tables name, and use explicit join syntax.

Upvotes: 2

Related Questions