Select in mysql grouping result by month

Asked

Viewed 41 times

0

Well I have the following table structure:

Categoria  |  DataOp        |  Tipo    | ValorOp
--------------------------------------------------
Carro      |  2012-01-14  |   1        | 10
Moto       |  2012-01-17  |   1        | 200
Venda      |  2012-03-15  |   2        | 500
Carro      |  2012-04-24  |   1        | 10
Moto       |  2012-04-10  |   1        | 35
Internet   |  2012-01-11  |   1        | 98

Well I want to make one select return me the sum of the totals of each month of type 1:

The result would have to be this:

 Mes    | Total
----------------------------
 01     | 308
 04     | 45

I think the select would look something like this:

SELECT
    DataOp,
    SUM(ValorOp)
FROM
    ResumoReceitaDespesas
GROUP BY DataOp

But here it groups by month.

--------------- Solution --------------

SELECT
    MONTH(DataOp),
    SUM(ValorOp)
FROM
    ResumoReceitaDespesas
WHERE Tipo = '1'
GROUP BY DataOp
mysql
  • 1

    I didn’t understand the group by city. Use the function MONTH() or maybe it’s better YEAR() and MONTH().

  • I fumbled at the end of the question. Anyway your solution worked.

1 answer

1


You can do it two ways,

SELECT
    MONTH(DataOp) as Mes,
    Tipo,
    SUM(ValorOp) as TotalOp
FROM
    ResumoReceitaDespesas
GROUP BY Mes, Tipo

SELECT
    MONTH(DataOp) as Mes,
    Tipo,
    SUM(ValorOp) as TotalOp
FROM
    ResumoReceitaDespesas
WHERE Tipo = '1'
GROUP BY Mes, Tipo

The first will bring all types grouped by month and the second only the first type.

Browser other questions tagged mysql

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