Simon Hodkinson Simon Hodkinson - 7 months ago 15
SQL Question

MySQL individual counts, from multiple tables, grouped by date

I have multiple MySQL tables, within one database, which each record transactions performed. Each table has a DATETIME field to record when each transaction occurred.

How do I construct a MySQL query, or procedure, to produce the count of transactions from each table, grouped by each date?

I want to see each days total from the start of the current month up to, and including, the current day.

In each table a row represents one transaction. So the columns are just the count of rows in each table, for each date.

E.g running such query, Today, would yield a table like;

26/04/2016 Total A Transactions, Total B Transactions, ...

25/04/2016 Total A Transactions, Total B Transactions, ...

....

01/04/2016 Total A Transactions, Total B Transactions, ...

Sample schema;

CREATE TABLE

tableA
(
uuid
BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
creationTime
DATETIME NOT NULL, ..

CREATE TABLE
tableB
(
uuid
BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
creationTime
DATETIME NOT NULL, ..

...

Tin Tin
Answer

I assume that tableA has at least one transaction a day.

select create_time, total_a_transaction, total_b_transaction, ...
from

(select date(creationTime) create_time, count(*) total_a_transaction
from tableA 
where year(creationTime) = year(curdate()) 
and month(creationTime) = month(curdate())
group by date(creationTime)) transaction_a

left join

(select date(creationTime) create_time, count(*) total_b_transaction
from tableB 
where year(creationTime) = year(curdate()) 
and month(creationTime) = month(curdate())
group by date(creationTime)) transaction_b

using (create_time)

left join ...
Comments