MySQL count as a column

Question

I have the following table structure

leads -id -fname -lname subcontractors -id -leads_id -fname -lname -status

Leads can have many subcontractors. The subcontractors.status tells if the subcontractor if active or suspended

Question is how do I select leads with a count column the tells how many active and suspended subcons they have?

I have this right now:

SELECT l.id, l.fname, l.lname, s.status FROM leads l INNER JOIN subcontractors s ON l.id = s.leads_id GROUP BY l.id ORDER BY l.id DESC;

My goal is to have something like this

id | fname | lname | active_subcon_count | suspended_subcon_count

D-Shih · Accepted Answer · 2019-06-03 02:18:10Z

You can try to use condition aggregate function COUNT with CASE WHEN

SELECT l.id, l.fname, l.lname, COUNT(CASE WHEN s.status = 'active' THEN 1 END) , COUNT(CASE WHEN s.status = 'suspended' THEN 1 END) from leads l INNER JOIN subcontractors s ON l.id = s.leads_id GROUP BY l.id , l.fname, l.lname ORDER BY l.id DESC;

or mysql can do SUM like this.

SELECT l.id, l.fname, l.lname, SUM(s.status = 'active') active_subcon_count , SUM(s.status = 'suspended') suspended_subcon_count from leads l INNER JOIN subcontractors s ON l.id = s.leads_id GROUP BY l.id , l.fname, l.lname ORDER BY l.id DESC;

Are you sure about the 2nd query? Never used the syntax before, tried and it does not work on my MySQL 5.7. It returns the same value as COUNT(*). COUNT(CASE WHEN expr THEN 1 END) or COUNT(IF(expr, 1, NULL)) inside works as expected.
@fifonik Thank for point out I forgot to modify SUM instead of COUNT

Collectives™ on Stack Overflow

MySQL count as a column

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related