Get totals by date range, relating to additional table

Question

The case is as follows, I have a table in Mysql referring to a company box, and I am creating a table in JAVA that will be a cost center. The database table has the columns:

id | data | descricao | id_tipoDespesa | valor |

i need the total value of each expense in a specific range of dates and also relate the id_tipoDespesa with the type of expenditure it represents.

the result I need is as in the example:

periodo:
de: data1 até: data2

despesa  | valor |
__________________
agua     | 1000  |
luz      | 2004  |
telefone | 503   |

i still know almost nothing about Mysql and no other database language, please try to explain as simple as possible kkkk

Answer 1

You have a lot of problems in one question.

Grouping and summing

One of them is the problem of grouping and totalization. You have several releases for id_tipos different but wants to show one per line with their totals.

The solution to this is to group with GROUP BY, and get the value with SUM:

SELECT   SUM(valor)
FROM     despesas
GROUP BY id_tipos;

Selecting range of dates

Since you want a specific interval, we need to limit the query above with one condition (WHERE). We could say WHERE data >= data_inicial AND data <= data_final, but to facilitate, there is an operator for this, the BETWEEN:

SELECT   SUM(valor)
FROM     despesas
WHERE    `data` BETWEEN "2016-04-01" AND "2016-07-30"
GROUP BY id_tipos
;

Relating to the other table

To query above already gives the totals, but lacked the type of expense in question. For this, we use the JOIN, conditioned with ON:

SELECT    tipo,
          SUM(valor) AS total
FROM      despesas
LEFT JOIN tipos ON tipos.id = id_tipos
WHERE     `data` BETWEEN "2016-04-01" AND "2016-07-30"
GROUP BY  id_tipos
;

See it working and do more tests on SQL Fiddle;

General considerations

• Important: The correct for fields with date and time is the format below:

    WHERE `data` BETWEEN "2016-04-01 00:00:00" AND "2016-07-30 23:59:59"
  

  note the 23:59:59 at the time of the final date, which is to pick up events that have occurred all day. If omit the time, the results.

• The GROUP BY separates the results from the SUM (or any other grouping functions) on separate lines according to the selected fields

• The AS total in the SUM serves to give a friendly nickname to the column, which will return with the name of total, to facilitate use in the application

• We use prefix on tipos.id in the ON for disambiguation, because the field id exists in both tables. We are making it clear that this is the id table tipos

Learn more about JOIN in

What is the difference between INNER JOIN and OUTER JOIN?