toolbox

MySQL queries

Some useful tricks for working with data from MySQL tables.

Aggregation

Getting aggregated data by period

Given a table like:

id	create_date	value
1	2018-12-01 19:00:00	10
2	2018-11-30 18:30:00	7
3	2018-11-20 12:15:00	3
4	2018-11-10 16:32:00	6

we want to obtain the totals from the value column grouped by different periods of time, using the available MySQL date and time functions.

By day

select 
	date(create_date) as day, 
	sum(value) as total
from my_table 
group by day
order by day desc

By week

select 
	yearweek(create_date) as week, 
	sum(value) as total
from my_table 
group by week
order by week desc

By month

Grouping by month is the only case where MySQL doesn’t have a dedicated date function, so we’ll use date_format to extract the year and month from the date:

select 
	date_format(create_date, "%Y-%m") as month, 
	sum(value) as total
from my_table 
group by month
order by month desc

Alternatively, we can use extract(year_month from create_date) instead of date_format.

By year

select 
	year(create_date) as year, 
	sum(value) as total
from my_table 
group by year
order by year desc

Aggregation on more than one column

Given a table like:

id	create_date	currency	value
1	2018-12-01 19:00:00	usd	10
2	2018-11-30 18:30:00	eur	7
3	2018-11-20 12:15:00	eur	3
4	2018-11-10 16:32:00	usd	6

You can group them by period and currency:

select 
	date(create_date) as day, 
	currency,
	sum(value) as total
from my_table 
group by day, currency
order by day desc

To give results in the form:

day	currency	total
2018-12-01	usd	10
2018-12-01	eur	7
2018-11-30	usd	5
2018-11-30	eur	3

Say you want to turn the values of the currency column into individual columns. I don’t know (yet) any other method of pivoting the chart than creating the columns manually:

select 
	date(create_date) as day, 
	sum(case when currency = 'usd' then value else 0 end) as usd,
	sum(case when currency = 'eur' then value else 0 end) as eur,
	sum(value) as total
from my_table 
group by day, currency
order by day desc

To obtain:

day	usd	eur	total
2018-12-01	10	7	17
2018-11-30	5	3	8

(This, of course, breaks down when there are several possible unique values for the column, but it’s not to bad when they’re a handful.)

This site is open source. Improve this page.