CODEWITHSUNDEEP
mysql
Chad Watkins
Chad Watkins

Reputation: 561

Loop multiple sums mysql

For a reporting tool im building I would like to be able to count how many "events" happened in each country that has been recorded so far I have this query:

SELECT 'country',
      COUNT(*) AS total,
      SUM(CASE WHEN `country` = 'USA' THEN 1 ELSE 0 END) AS USCount,
      SUM(CASE WHEN `country` = 'Canada' THEN 1 ELSE 0 END) AS CanadaCount
  FROM events WHERE `date` > '2015-06-01' AND `date` < '2015-08-01'

The only problem with this is that the cases will start to get enormous when I start adding other countries into the mix. Is there a way I can loop over country to retrieve all of the various counts for each country? Thanks.

Upvotes: 0

Views: 20

Answers (2)

Abhishek Ginani
Abhishek Ginani

Reputation: 4751

Group it by the country name to get the count country wise 

SELECT country,
  COUNT(*) AS total
 FROM events WHERE `date` > '2015-06-01' AND `date` < '2015-08-01' group by country;

Upvotes: 2

Tarik
Tarik

Reputation: 11209

Try the following SQL statement:

Select country, count (*)
From events
Group by country

Upvotes: 2

Related Questions