CODEWITHSUNDEEP
mysqlsqljoin
iammadab
iammadab

Reputation: 3

mysql - Apply condition only to sum not entire query

I have two tables, one for artists and another for their artworks.

When querying all the artists in the system I want to compile a report of their artworks. Currently, I have this.

select artists.*,
    sum(case when artworks.status = 'rejected' then 1 else 0 end) as rejectedCount,
    sum(case when artworks.status = 'pending' then 1 else 0 end) as pendingCount,
    sum(case when artworks.status = 'verified' then 1 else 0 end) as verifiedCount
from artists,artworks
where artists.id=artworks.artist_id
group by artists.id;

This works as intended, but some artists don't have any artwork and as such they are excluded from the result. What I want is to list all the artists even if they don't have any artwork, zeroing out the counts for them.

Upvotes: 0

Views: 41

Answers (1)

nbk
nbk

Reputation: 49375

Use a LEFT JOIN and Count the NULL

CREATE TABLE artists(id int)

INSERT INTO artists VALUES (1),(2),(3),(4)

CREATE TABLE artworks (artist_id int, Status varchar(10))

INSERT INTO artworks VALUES (1,'rejected'),(2,'pending'),(3,'verified')

select artists.*,
    sum(case when artworks.status = 'rejected' then 1 else 0 end) as rejectedCount,
    sum(case when artworks.status = 'pending' then 1 else 0 end) as pendingCount,
    sum(case when artworks.status = 'verified' then 1 else 0 end) as verifiedCount,
    SUM(CASE WHEN artworks.status IS NULL THEN 1 ELSE 0 END) as no_work 
from artists LEFT JOIN artworks ON  artists.id=artworks.artist_id
group by artists.id;

id | rejectedCount | pendingCount | verifiedCount | no_work
-: | ------------: | -----------: | ------------: | ------:
 1 |             1 |            0 |             0 |       0
 2 |             0 |            1 |             0 |       0
 3 |             0 |            0 |             1 |       0
 4 |             0 |            0 |             0 |       1

db<>fiddle here

Upvotes: 2

Related Questions