How to group by weeks using sql?

Question

How to group by weeks using sql?

Navigation

#1 by (3 votes)
#2 by (3 votes)
#3 by (2 votes)

3

I have the following problem. I have several requests, and I want to group them for weeks. I have already seen several examples, but almost none helped me, I would like to know if there is a function that when placed in Group by the result of the query would look like this: Ex

DATE No.Requestments
01/01/2016 32
1/8/2016 44

Thank you so much.

mysql sql query postgresql

asked by anonymous 07.03.2016 / 13:04

3 answers

3

In MySQL, using the function week , can be done as follows:

SELECT YEAR(dia) as ano,
DATE_ADD(dia, INTERVAL(1-DAYOFWEEK(dia)) DAY) as semana,
SUM(requisicoes) as total_requisicoes
FROM tabela GROUP BY ano, WEEK(dia)

See the example in sqlfiddle

07.03.2016 / 13:23

2

You can group by week and display the full date:

(MySQL)

SELECT
    min(data_requisicao) as 'Semana Requisição',
    count(*)             as 'Contagem Requisições'
FROM
    tbl_requisicoes
GROUP BY 
    WEEK(data_requisicao)

07.03.2016 / 13:24

How to customize the side borders of a DIV? Why are not you loading this image in javascript?

score 3 · Accepted Answer

Using the function date_trunc of Postgres itself:

SELECT
    date_trunc('week', dt_registro),
    SUM(vl_total)
FROM
    tb_nota
WHERE
    dt_registro BETWEEN '01/02/2016' AND '01/03/2016'
GROUP BY 
    1
ORDER BY 
    1

week arrow to week