How to group by month with SQL?

Question

How to group by month with SQL?

Navigation

#1 by (5 votes)

6

I have a table, for example, with an attribute nome and data (for example only).

I would like to generate a query that returns the quantity of each row grouped by name and month:

Nome Janeiro Fev Março Abril Maio ...
João  1       0   3     4      5  ...
Lucas 0       2   4     10     1  ...

I can do this separate query:

SELECT nome, count(extract month from data) jan 
FROM tabela 
WHERE extract month FROM data = 1 
GROUP BY nome

I would like to do everything in one query.

sql sql-select postgresql

asked by anonymous 15.05.2014 / 22:42

1 answer

Reorder the categories in a data frame Correct Statement Classes Model MVC Ninject

score 5 · Accepted Answer

Here is the solution: I posted it on the link , simulating with PostgreSQL 9.3.1, but I believe it works with all banks, with Oracle , SQL Server and PostgreSQL will work.

Explaining the concept: how do you want to know how many records have each person in the month, group SQL per person, and then do the case with sum to have the rule that I want and show the number of items per month grouped per person .

The good thing about this SQL is that it is simple and does not weigh in the database.

Template

create table temp 
(
  nome varchar(50),
  data date
)

Test data:

insert into temp values ('Maria', '2014-02-01');
insert into temp values ('Maria', '2014-02-01');
insert into temp values ('Maria', '2014-02-01');
insert into temp values ('Maria', '2014-03-01');
insert into temp values ('Maria', '2014-04-01');
insert into temp values ('Maria', '2014-04-01');

insert into temp values ('João', '2014-04-01');
insert into temp values ('João', '2014-06-01');
insert into temp values ('João', '2014-07-01');
insert into temp values ('Maria', '2014-04-01');

SQL for the result:

SELECT 
  nome, 
  sum(case when EXTRACT(MONTH FROM data)= 1 then 1 else 0 end) as Jan,
  sum(case when EXTRACT(MONTH FROM data)= 2 then 1 else 0 end) as Fev,
  sum(case when EXTRACT(MONTH FROM data)= 3 then 1 else 0 end) as Mar,
  sum(case when EXTRACT(MONTH FROM data)= 4 then 1 else 0 end) as Abr,
  sum(case when EXTRACT(MONTH FROM data)= 5 then 1 else 0 end) as Mai,
  sum(case when EXTRACT(MONTH FROM data)= 6 then 1 else 0 end) as Jun,
  sum(case when EXTRACT(MONTH FROM data)= 7 then 1 else 0 end) as Jul,
  sum(case when EXTRACT(MONTH FROM data)= 8 then 1 else 0 end) as Ago,
  sum(case when EXTRACT(MONTH FROM data)= 9 then 1 else 0 end) as Set,
  sum(case when EXTRACT(MONTH FROM data)= 10 then 1 else 0 end) as Out,
  sum(case when EXTRACT(MONTH FROM data)= 11 then 1 else 0 end) as Nov,
  sum(case when EXTRACT(MONTH FROM data)= 12 then 1 else 0 end) as Dez
FROM 
  temp
group by
  nome