How to group by month with SQL?

Asked

Viewed 5,097 times

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 amount 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 know how to do this separate consultation:

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 consultation.

sql postgresql select-sql
  • 1

    What is your expected result?

  • Marlon, the expected result is the table I put up. My separate consultation will return only the month of January, I would like the other months to be in the same consultation tbm.

  • http://forum.imasters.com.br/topic/515040-listar-quantidade-de-pedidos-por-ms-sem-repetir-client/ see if it helps

1 answer

5


Here is the solution: I posted it on http://sqlfiddle.com/#! 15/a04f8/8, simulating with Postgresql 9.3.1, but I believe it works with all databases, with Oracle, SQL Server and Postgresql will work.

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

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

Model

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

  • Exactly, thank you Marlon.

Browser other questions tagged sql postgresql select-sql

You are not signed in. Login or sign up in order to post.