I store hits. My table looks like this:
ID | time | Country
--------------------------------
1 | 01:00:00 | France
2 | 01:00:00 | France
3 | 01:00:00 | Brazil
4 | 01:00:00 | USA
5 | 02:00:00 | USA
This is my query:
SELECT COUNT(*) as total_hits, HOUR(time) as hour, Country
FROM hits
WHERE time >= CURDATE()
GROUP BY HOUR(time)
It does count how many hits I got and groups it by hours:
time | total_hits
---------------------------
01:00:00 | 4
02:00:00 | 1
I want one result grouped by time
column and another grouped by countries
column.
This is what I need:
1) Grouped by hours like this (same like above)
time | total_hits
---------------------------
01:00:00 | 4
02:00:00 | 1
2) AND grouped by countries like this
country | total_hits
---------------------------
France | 2
USA | 2
Brazil | 1
I could do:
SELECT
COUNT(*)
, HOUR(time)
, COUNT(IF( Country = 'France', Country, null)) AS France
, COUNT(IF( Country = 'USA', Country, null)) AS USA
, COUNT(IF( Country = 'Brazil', Country, null)) AS Brazil
FROM hits
WHERE time >= CURDATE()
GROUP BY HOUR(time)
Or alternatively with CASE
or SUM(Country = 'France') AS France
.
But in the country column there are more than just 3 countries. If I would do this with every country my query would be very long.
I could do this:
SELECT COUNT(*), Country, HOUR(time)
FROM hits
WHERE time >= CURDATE()
GROUP BY Country, HOUR(time)
But the ouput will be something like this:
time | country | total_hits
---------------------------------------
01:00:00 | France | 2
01:00:00 | USA | 1
01:00:00 | Brazil | 1
02:00:00 | USA | 1
However I need an output like mentioned above.
So basically I need these queries in one query:
1) Group by HOUR (same like mentioned above)
SELECT COUNT(*) as total_hits, HOUR(time) as hour, Country
FROM hits
WHERE time >= CURDATE()
GROUP BY HOUR(time)
2) AND Group by Country
SELECT COUNT(*) as total_hits
FROM hits
WHERE time >= CURDATE()
GROUP BY Country
Performance is important. The database has millions of entries. Maybe MySQL is not the best way for this problem?