You have several problems in one issue.
Grouping and Summing
One of them is the problem of grouping and totalizing. You have multiple releases for id_tipos different, but you want to show one per line with your 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 Date Range
As you want a specific range, we need to limit the query above with a condition ( WHERE ). We could say WHERE data >= data_inicial AND data <= data_final , but to make it easier, there is an operator for this, BETWEEN :
SELECT SUM(valor)
FROM despesas
WHERE 'data' BETWEEN "2016-04-01" AND "2016-07-30"
GROUP BY id_tipos
;
Relating to another table
The query above already gives the totals, but lacked the type of expense in question. For this, we use 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 working and testing the SQL Fiddle ;
General considerations
-
Important : The correct for fields with date and time is the following format:
WHERE 'data' BETWEEN "2016-04-01 00:00:00" AND "2016-07-30 23:59:59"
Note the 23:59:59 at the end date time, which is to catch events occurring all day long. If you omit the time, the results.
The GROUP BY separates the results of SUM (or any other grouping functions) into separate rows according to the selected fields
-
The AS total no SUM serves to give a friendly nickname for the column, which will return with the name of total , to facilitate use in the application
-
We use prefix in tipos.id in ON for disambiguation, since the id field exists in both tables. We are making it clear that this is id of table tipos
Learn more about JOIN in
What's the difference between INNER JOIN and OUTER JOIN?
