Select count of multiple values from multiple tables

Question

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

Never use commas in the FROM clause. Always use proper, explicit, standard JOIN syntax. — Gordon Linoff
– Gordon Linoff, Commented Nov 11, 2019 at 12:42
it's little hard to track all your tables if you can display the tables you want to select from as the same way you displayed the result it will be better — Amado
– Amado, Commented Nov 11, 2019 at 12:52

halfer · Accepted Answer · 2020-03-02 01:15:12Z

2

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.

edited Mar 2, 2020 at 1:15

halfer

20.2k20 gold badges110 silver badges207 bronze badges

answered Nov 11, 2019 at 13:08

ScaisEdge

134k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tekos Over a year ago

Yes, adding distinct worked for count(distinct tr.id)! Will also check my syntax and replace commas with joins. Cheers!

Collectives™ on Stack Overflow

Select count of multiple values from multiple tables

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related