How merge these two sql queries in mysql?-CodePudding

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

CodePudding user response：

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
;

CodePudding user response：

Based on your comments and edits, you want to display the title and the amount of Current Artists and Old Artists. I just modified your two queries to get this;

  Select title, Current Artists, Old Artists from (
    Select p_band.title as 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 ) a
    
    Join (
   Select p_band.title as ptitle,count(p_artist.id) As 'Old 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 ) b 
   On a.title = b.ptitle

The first select query it to get your title and Current Artists then Join the second select with the count of Old Artists.