MySQL (PHP): COUNT countries and GROUP BY time

Question

I store hits. My table looks like this:

    ID |   time   |   Country   
--------------------------------
    1  | 01:00:00 |    France
    2  | 01:00:00 |    Brazil
    3  | 01:00:00 |    USA
    4  | 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:

01:00:00
Total hits: 3

02:00:00
Total hits: 1

But this is what I need:

01:00:00
Total hits: 3

02:00:00
Total hits: 1
-----------

France: 1
USA: 2
Brazil: 1

I know 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.

Edit

I could do this as Alex Monthy answer:

SELECT COUNT(*), Country, HOUR(time)
FROM hits 
WHERE time >= CURDATE() 
GROUP BY Country, HOUR(time)

But the ouput will be something like this:

01:00:00
Total hits: 1
Country: France

01:00:00
Total hits: 1
Country: Brazil

01:00:00
Total hits: 1
Country: USA

02:00:00
Total hits: 1
Country: USA

However I need an output like mentioned above.

TL;DR: I want one result grouped by time and another grouped by countries.

I now have the impression that you are trying to get two structurally different results in one query - is that true? As I see it, you want one query to group and count by country, and one to group and count by hour(time). For that you need two different queries. — Alex Monthy, Jun 10 '14 at 16:44
If you have a large (and I would assume, variable) number of countries then you can't do it as it require a number of columns that would vary row by row. The solution I posted yesterday will give you the counts per hour and the counts per county within that hour, but bringing back the counts for each row concatenated into one a column on that row. — Kickstart, Jun 11 '14 at 10:12
So you actually want a result set grouped by hours, then another result set grouped by country, right? — RandomSeed, Jun 12 '14 at 17:35

score 1 · Answer 1 · answered Jun 10 '14 at 12:20

1

As you said yourself:

I need it to be grouped by time (hours) and countries

Just do it:

SELECT COUNT(*), Country, HOUR(time)
FROM hits 
WHERE time >= CURDATE() 
GROUP BY Country, HOUR(time)

answered Jun 10 '14 at 12:20

Alex Monthy

1,827
1
14
24

I don't need it like this. Please look at my edit. – yoshi Jun 10 '14 at 14:30

score 1 · Answer 2 · answered Jun 10 '14 at 13:10

1

Probably need to use a cross join to a sub query that gets the counts per country / hour, then join that to hits to get the overall counts, using GROUP_CONCAT to give the country counts for that hour

Something like this :-

SELECT hour(hits.time), COUNT(DISTINCT hits.id), GROUP_CONCAT(DISTINCT CONCAT_WS(' - ', sub0.Country, sub0.country_count))
FROM hits
INNER JOIN
(
    SELECT hour(hits.time) as the_hour, Country, COUNT(*) AS country_count
    FROM hits
    GROUP BY the_hour, Country
) sub0
ON hour(hits.time) = sub0.the_hour
GROUP BY the_hour

answered Jun 10 '14 at 13:10

Kickstart

21,403
2
21
33

This is not the output I want. However I guess what I want is not possible. But I am wondering how for example GoogleAnalytics does it (even though there queries are way more complex..). I am thinking now about going with completely seperate queries but I am not sure how worse for performance that is. Performance is acutally very important because the table has billion of entries. I would appreciate some advice... – yoshi Jun 11 '14 at 15:26
I want to do this with the column `browser` (contains values like Firefox, Chrome etc.) too. But I think I will do browser in the same query because there are only 5 main browsers so I could do `SUM(browser = 'Firefox') AS Firefox`... – yoshi Jun 11 '14 at 15:43
If a fixed number it is easy enough (although not that efficient). But with a variable number of columns it is not possible directly. You either need to do 2 queries, or do it similar to the way I have and then explode the resulting column into separate fields. – Kickstart Jun 11 '14 at 16:23
Not sure if I did get it right. But the number of column is fixed. I store all the countries in the `country` column – yoshi Jun 11 '14 at 20:03
The countries are only fixed if the possible ones never change. As soon as they do you have to modify the query, or put up with results which do not add up (ie, if Germany were added, the count of each country would no longer match the total count until you added Germany as a column), and it also forces you to do the odd processing to only count certain rows to add up for a column. – Kickstart Jun 12 '14 at 08:14

score 0 · Answer 3 · answered Jun 10 '14 at 12:10

0

really not sure but have you tried

SELECT COUNT(*), HOUR(time)
FROM hits 
WHERE time >= CURDATE() 
GROUP BY Country

answered Jun 10 '14 at 12:10

Cratein

453
5
15

I don't want to group by only countries. I need it to be grouped by time (hours) and countries. – yoshi Jun 10 '14 at 12:11
Oh i thought you needed both since you needed : France: 1 USA: 2 Brazil: 1 – Cratein Jun 10 '14 at 12:12
I need both. You are right. But with the query above it is only grouped by country. – yoshi Jun 10 '14 at 12:14
Ah yeah definitely... my bad – Cratein Jun 10 '14 at 12:15
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient [reputation](http://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](http://stackoverflow.com/help/privileges/comment). – David Brossard Jun 10 '14 at 12:41
@DavidBrossard It's a legitimate good faith attempt to answer the question, even if it's not actually correct. Not an answer flags are for answers that don't make an attempt to answer the question being asked, things like "I'm also having this issue", asking their own question entirely, saying "thanks" in response to somebody who did actually provide an answer. – Anthony Grist Jun 10 '14 at 12:49
I couldn't comment the question because i don't have the 50 reputation requested. – Cratein Jun 10 '14 at 13:05

bancer · Answer 4 · 2014-06-10T12:41:50.900

0

SELECT COUNT(*) AS `totals`, country, HOUR(time) AS `hour`
FROM hits 
WHERE time >= CURDATE() 
GROUP BY country, HOUR(time) WITH ROLLUP;

This will give you not only groups by country and hour but and subtotals by countries with grand total.

http://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html

edited Jun 10 '14 at 12:41

answered Jun 10 '14 at 12:35

bancer

7,475
7
39
58

score 0 · Answer 5 · answered Jun 10 '14 at 12:49

0

Try like this

SELECT
DISTINCT Country,
COUNT(HOUR (time))
FROM
hits
WHERE
time >= CURDATE() 
GROUP BY country

answered Jun 10 '14 at 12:49

A l w a y s S u n n y

36,497
8
60
103

MySQL (PHP): COUNT countries and GROUP BY time

5 Answers5