CODEWITHSUNDEEP
mysqlsql
FrancescoElba
FrancescoElba

Reputation: 33

Why did I sum the wrong result?

I'd like to have an advice for this SUM that doesn't work as expected. I have to SUM data from the same day and display it in a table. I did a query like this:

SELECT * FROM giorni WHERE punto = '$punto' && STR_TO_DATE(giorno, '%d/%m/%Y') BETWEEN   STR_TO_DATE('" . $d1 . "', '%d/%m/%Y') AND STR_TO_DATE('" . $d2 . "', '%d/%m/%Y') ORDER BY data";

This is the 'simple' one and it displays every single row, I have to put a SUM in it to make it show every day but SUM data from the same day.

These 2 rows must be shown as one: enter image description here

Can anyone give me any help to create this query?

Upvotes: 2

Views: 4121

Answers (3)

D-Shih
D-Shih

Reputation: 46249

if I understand correctly you can try this.

You can't put multiparameter in SUM function.

When you use Aggregate function you need to add non-Aggregate columns inGROUP BY caluse.

 SELECT data,giorno,SUM(a1) as a1, SUM(a2) as a2, SUM(a3) as a3 , SUM(a4) as a4 
 FROM giorni 
 WHERE punto = '$punto' && STR_TO_DATE(giorno, '%d/%m/%Y') 
 BETWEEN STR_TO_DATE('" . $d1 . "', '%d/%m/%Y') AND STR_TO_DATE('" . $d2 . "', '%d/%m/%Y') 
 GROUP BY data,giorno
 ORDER BY data";

Upvotes: 2

thefolenangel
thefolenangel

Reputation: 1060

SUM is an aggregate function. It will calculate the total for each group. + is used for calculating two or more columns in a row.

Example:

ID  VALUE1  VALUE2
===================
 1   1       2
 1   2       2
 2   3       4
 2   4       5

So the following query:

SELECT  ID, VALUE1 + VALUE2
FROM    TableName

will result in:

ID, VALUE1 + VALUE2
1   3
1   4
2   7
2   9

However this query:

SELECT  ID, SUM(VALUE1 + VALUE2)
FROM    tableName
GROUP   BY ID

Will result in:

ID, SUM(VALUE1 + VALUE2)
1   7
2   16

Upvotes: 3

david
david

Reputation: 3228

Try this

SELECT data,giorno,SUM(a1),SUM(a2),SUM(a3),SUM(a4)
FROM giorni
WHERE punto = '$punto' && STR_TO_DATE(giorno, '%d/%m/%Y') BETWEEN STR_TO_DATE('" . $d1 . "', '%d/%m/%Y') AND STR_TO_DATE('" . $d2 . "', '%d/%m/%Y')
GROUP BY data
ORDER BY data

This code is assuming that you don't combine the four fields. Put SUM into the fields that you want to sum.

Upvotes: 0

Related Questions