Can I use one query to count two columns in SQL

Question

I have

office

office_id    name
--------- -----------------
        1  office1
        2  office2
        3  office3

person

uid  office_id age gender
---------------------------
  1          1  20   male
  2          1  20   female
  3          1  20   male
  4          1  21   male
  5          2  20   male
  6          3  20   male

Is it possible I can use ONE query to get

office_id    name age_20 male
-----------------------------
        1 office1      3    3  
        2 office2      1    1
        3 office3      1    1

Answer 1

Yes, you can. MySQL support boolean arithmethic and I think this is the shortest way to do it. If you want a more RDBMS friendly, use CASE WHEN age = 20 THEN 1 ELSE 0 END.

SELECT  a.office_ID,
        a.name,
        SUM(age = 20) age_20,
        SUM(gender = 'Male') male
FROM    office a
        LEFT JOIN person b
            ON a.Office_ID = b.office_ID
GROUP   BY a.office_ID, a.name

• SQLFiddle Demo