SQL Multiple Count In One Query

Question

I am trying to run the following query to return a list of all months with the count of English and Scottish users. I am not sure how to run multiple Count queries along side each other.

SELECT month(Date) as month
     , COUNT(*) as scottishCount 
  FROM users userTable 
 WHERE year(Date) = 2015 
   AND userTable.UserID in 
 (select nationsTable.UserID 
    from users nationsTable 
   where nationsTable.Nation = 'Scotland'), COUNT(*) as englishCount FROM users userTable WHERE year(Date)=201 AND userTable.UserID in (select nationsTable.UserID from nations nationsTable where nationsTable.Nation= 'England') GROUP BY month(DateClicked);

Thanks in advance,

Fred

Please share sample data with table structures. – Kazi Mohammad Ali Nur Romel Mar 08 '21 at 17:41 — Kazi Mohammad Ali Nur Romel, Mar 08 '21 at 17:41
Please explain DateClicked and Date column. – Kazi Mohammad Ali Nur Romel Mar 08 '21 at 17:42 — Kazi Mohammad Ali Nur Romel, Mar 08 '21 at 17:42
Is there anything not working with the given code? – Nico Haase Mar 09 '21 at 09:41 — Nico Haase, Mar 09 '21 at 09:41

score 0 · Answer 1 · answered Mar 08 '21 at 17:45

You must move your conditions to the SELECT clause in order to count conditionally. MySQL treats true as 1 and false as 0, so you can use SUM to count matches. It doesn't seem necessary to select from the users table multiple times:

SELECT
  MONTH(date) AS month, 
  SUM(nation = 'Scotland') AS scottish_count,
  SUM(nation = 'England') AS english_count
FROM users
WHERE YEAR(date) = 2015 
GROUP BY MONTH(date)
ORDER BY MONTH(date);

SQL Multiple Count In One Query

1 Answers1