4

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?

Improve this question
2
  • I find the way you are displaying this output to be confusing. Can you put it in a column sort of output method?
    – Zane
    Jun 19, 2014 at 20:40
  • I did edit my question. Hope it's a little bit less confusing.
    – yoshi
    Jun 19, 2014 at 21:03

1 Answer 1

Reset to default
2

I don't think I can help you with the formatting, but the query you need is this one:

PROPOSED QUERY

SELECT
    IFNULL(Hour,CONCAT('Total for ',IFNULL(Country,'All Countries'))) Statistic,
    COUNT(1) TotalHits
FROM
(
    SELECT Country,(time - INTERVAL MOD(UNIX_TIMESTAMP(time),3600) SECOND) Hour
    FROM hits WHERE time >= (DATE(NOW()) + INTERVAL 0 SECOND)
) AA
GROUP BY Country,Hour WITH ROLLUP;

YOUR SAMPLE DATA

USE test
DROP TABLE IF EXISTS hits;
CREATE TABLE hits
(
    id int not null auto_increment,
    time datetime,
    country varchar(20),
    PRIMARY KEY (id)
);
INSERT INTO hits (time,Country) VALUES
('2014-06-19 01:00:00','France'),
('2014-06-19 01:00:00','Brazil'),
('2014-06-19 01:00:00','USA'),
('2014-06-19 02:00:00','USA');

YOUR SAMPLE DATA LOADED

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS hits;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE hits
    -> (
    ->     id int not null auto_increment,
    ->     time datetime,
    ->     country varchar(20),
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO hits (time,Country) VALUES
    -> ('2014-06-19 01:00:00','France'),
    -> ('2014-06-19 01:00:00','Brazil'),
    -> ('2014-06-19 01:00:00','USA'),
    -> ('2014-06-19 02:00:00','USA');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM hits;
+----+---------------------+---------+
| id | time                | country |
+----+---------------------+---------+
|  1 | 2014-06-19 01:00:00 | France  |
|  2 | 2014-06-19 01:00:00 | Brazil  |
|  3 | 2014-06-19 01:00:00 | USA     |
|  4 | 2014-06-19 02:00:00 | USA     |
+----+---------------------+---------+
4 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SELECT
    ->     IFNULL(Hour,CONCAT('Total for ',IFNULL(Country,'All Countries'))) Statistic,
    ->     COUNT(1) TotalHits
    -> FROM
    -> (
    ->     SELECT Country,(time - INTERVAL MOD(UNIX_TIMESTAMP(time),3600) SECOND) Hour
    ->     FROM hits WHERE time >= (DATE(NOW()) + INTERVAL 0 SECOND)
    -> ) AA
    -> GROUP BY Country,Hour WITH ROLLUP;
+-------------------------+-----------+
| Statistic               | TotalHits |
+-------------------------+-----------+
| 2014-06-19 01:00:00     |         1 |
| Total for Brazil        |         1 |
| 2014-06-19 01:00:00     |         1 |
| Total for France        |         1 |
| 2014-06-19 01:00:00     |         1 |
| 2014-06-19 02:00:00     |         1 |
| Total for USA           |         2 |
| Total for All Countries |         4 |
+-------------------------+-----------+
8 rows in set (0.00 sec)

mysql>

I'll leave the formatting to you :-)

CAVEAT: Please make sure you have a time index on the table

ALTER TABLE hits ADD INDEX time_index (time);

An additional query could be

SELECT
    IFNULL(Country,CONCAT('Total for ',IFNULL(Hour,DATE(NOW())))) Statistic,
    COUNT(1) TotalHits
FROM
(
    SELECT Country,(time - INTERVAL MOD(UNIX_TIMESTAMP(time),3600) SECOND) Hour
    FROM hits WHERE time >= (DATE(NOW()) + INTERVAL 0 SECOND)
) AA
GROUP BY Hour,Country WITH ROLLUP;

whose output would be

+-------------------------------+-----------+
| Statistic                     | TotalHits |
+-------------------------------+-----------+
| Brazil                        |         1 |
| France                        |         1 |
| USA                           |         1 |
| Total for 2014-06-19 01:00:00 |         3 |
| USA                           |         1 |
| Total for 2014-06-19 02:00:00 |         1 |
| Total for 2014-06-19          |         4 |
+-------------------------------+-----------+
7 rows in set (0.00 sec)

UPDATE 2014-06-21 19:07 EDT

Perhaps the the two queries combined with UNION will work for you

SELECT
    IFNULL(Country,CONCAT('Total for ',IFNULL(Hour,DATE(NOW())))) Statistic,
    COUNT(1) TotalHits
FROM
(
    SELECT Country,(time - INTERVAL MOD(UNIX_TIMESTAMP(time),3600) SECOND) Hour
    FROM hits WHERE time >= (DATE(NOW()) + INTERVAL 0 SECOND)
) AA
GROUP BY Hour,Country
UNION
SELECT
    IFNULL(Hour,CONCAT('Total for ',IFNULL(Country,'All Countries'))) Statistic,
    COUNT(1) TotalHits
FROM
(
    SELECT Country,(time - INTERVAL MOD(UNIX_TIMESTAMP(time),3600) SECOND) Hour
    FROM hits WHERE time >= (DATE(NOW()) + INTERVAL 0 SECOND)
) AA
GROUP BY Country,Hour WITH ROLLUP;

I removed the WITH ROLLUP from the first one so that the total comes out once

Improve this answer
1
  • First of all, Thank you very much! Incredible, really! You offered 2 queries. But both are only "50%" of what I want. The first query gives total for countries. The other gives total for dates. But I need both. I hope I am clear.
    – yoshi
    Jun 19, 2014 at 21:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.