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
Your table definition says
...
ageband character(12),
...
which means the values in there look like '18-24 '
instead of '18-24'
. This way, the items from the VALUES
list do not match the values in the table, therefore you get an empty table as result.
If you change the column type to a bit more senseful text
(which trims the values, too, as Erwin points it out):
ALTER TABLE activities_in_localities_asc ALTER COLUMN ageband TYPE text;
you will get your desired results.
Best Answer
Give this a try,tested on my machine
First make sure
GROUP_CONCAT
has plenty of space:Link
Edit