How to return the sum of the business value column, month by month, based on the starting data_column?

Asked

Viewed 70 times

0

I have the following table:

Tabela

I need an SQL statement that returns the sum of the business value_value column, month by month, based on the start data_column.

Example:

Wanted OP result

I need the name of the month and the total.

mysql sql

4 answers

4


SELECT SUM(valor_negocio),
       MONTHNAME(STR_TO_DATE(MONTH(data_inicio), '%m'))
FROM negocio
GROUP BY MONTH(data_inicio)

  • Felipe, this code is pure sql ? IE, works for any bank ?

  • @alessandremartins is using functions they may not have in other banks. You have informed the mysql tag. These functions belong to it. Note. Pay attention to the years.

  • was as the friend above mentioned, I used the syntax in mysql, it may not work in other banks. Gives a check.

1

Complementing the reply of Henrique:

SELECT SUM(valor_negocio) AS SOMA, MONTH(data_inicio) AS MES 
FROM negocio GROUP BY MONTH(data_inicio)

In addition, if the selected data is greater than one year, you will also need to group by year:

SELECT SUM(valor_negocio) AS SOMA, MONTH(data_inicio) AS MES 
FROM negocio GROUP BY MONTH(data_inicio), YEAR(data_inicio)

1

Separating by the years, but without displaying them:

Select
   MONTHNAME(DATE_FORMAT(data_inicio,'%Y-%m-01')) as mes,
   sum(valor_negocio)
from negocio
group by DATE_FORMAT(data_inicio,'%Y-%m-01')
order by DATE_FORMAT(data_inicio,'%Y-%m-01');

Showing the years:

Select
   Year(data_inicio) as ano,
   MONTHNAME(DATE_FORMAT(data_inicio,'%Y-%m-01')) as mes,
   sum(valor_negocio)
from negocio
group by ano, DATE_FORMAT(data_inicio,'%Y-%m-01')
order by DATE_FORMAT(data_inicio,'%Y-%m-01');

I put in Sqlfiddle: http://sqlfiddle.com/#! 9/85c744/3

-1

Good morning,

Try:

select sum(valor_negocio) from negocio group by data_inicio
  • 1

    but that way the name of the month won’t appear, I need it to appear

  • 2

    I didn’t understand why you denied the answer, you asked the script to return the sum of the value. To display other database fields, just add in sql , as Anderson showed.

Browser other questions tagged mysql sql

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