Skip to content
Advertisement

How merge these two sql queries in mysql?

I want to merge below two queries and display two p_artist.id count based on the condition of each band name but I am not getting any clue. Can anyone help me, please?

Thanks

Select p_band.title,count(p_artist.id) As 'Current Artists'
FROM p_artist INNER JOIN p_member ON p_artist.id = p_member.artist_id
INNER JOIN p_band ON p_band.id = p_member.band_id
WHERE p_member.leave_date IS NULL
GROUP BY p_band.title
;

Select p_band.title,count(p_artist.id) As 'Artists'
FROM p_artist INNER JOIN p_member ON p_artist.id = p_member.artist_id
INNER JOIN p_band ON p_band.id = p_member.band_id
WHERE p_member.leave_date IS NOT NULL 
GROUP BY p_band.title
;


I want to display the record like this:

title           Current Artists    Old Artists
classical band     23             19

Advertisement

Answer

I mean I want to have two-column of count ids

The requirements are not clear at all and I had originally typed a separate answer, but in light of the comment above I think you’re looking for something like this.

It sums each case statement into its own column.

Select 
    p_band.title,
    SUM(CASE WHEN p_member.leave_date IS NULL THEN 1 ELSE 0 END) as null_count, 
    SUM(CASE WHEN p_member.leave_date IS NOT NULL THEN 1 ELSE 0 END) as not_null_count
FROM p_artist 
INNER JOIN p_member ON p_artist.id = p_member.artist_id
INNER JOIN p_band ON p_band.id = p_member.band_id
GROUP BY p_band.title
;
Advertisement