How to group for weeks using sql?

Asked

Viewed 2,223 times

3

I have the following problem . I have several requests , and I want to group them for weeks. I’ve seen several examples ,almost none helped me, I wonder if there is a function that when you put it in Group by the result of the query would come out like this:Ex

DATE Number of requests
01/01/2016 32
08/01/2016 44

Thanks until more.

mysql sql database postgresql query
  • 1

    You need the week count date to start from the first day of each month?

  • Not necessarily, my problem was: how many requests each executor made and then generate a graph.

3 answers

3


Using the Function date_trunc of Postgres himself :

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 for week

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

2

You can group by week and present 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)

Browser other questions tagged mysql sql database postgresql query

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