Re: sum() problems - I don't understand

[gerald_clark]

Why would you expect they should have the same results?
The second query contains a join and may have many times more rows in 
the result set.

PaT! wrote:

Dear All,

I have these two queries which are supposed to return the same results
for the fields with the same name (ex query 1 sum1 = query 2 sum1):
The first query gives me back the correct result for the sum():

SELECT           DATE_FORMAT(ordini.dataord,'%d-%m-%Y') date1, 
                SUM(ordini.totale) sum1,
                SUM(ordini.quantita) sum2,
                ordini.cod,
                ordini.fromprev
FROM            ordini
WHERE           ordini.numordine = 2302 
GROUP BY        ordini.numordine;

+------------+---------+------+------------+----------+
| date1      | sum1    | sum2 | cod        | fromprev |
+------------+---------+------+------------+----------+
| 29-05-2003 | 436.498 |   10 | SC0001GP   |     2666 |
+------------+---------+------+------------+----------+ 

this other one gives me problems with the sum(), wrong result.

SELECT           DATE_FORMAT(ordini.dataord,'%d-%m-%Y') date1, 
                SUM(ordini.totale) sum1,
                SUM(ordini.quantita) sum2,
                ordini.cod,
                ordini.fromprev,
                carello.numprev,
                DATE_FORMAT(carello.dataordine,'%d-%m-%Y') date2 
FROM             ordini, carello 
WHERE            ordini.numordine = 2302 
AND              carello.numprev = ordini.fromprev 
GROUP BY         ordini.numordine, carello.numprev;

+------------+----------+------+---------+----------+---------+---------
---+
| date1      | sum1     | sum2 | cod     | fromprev | numprev | date2
|
+------------+----------+------+---------+----------+---------+---------
---+
| 29-05-2003 | 3491.984 |   80 | SC0001GP|     2666 |    2666 |
28-05-2003 |
+------------+----------+------+---------+----------+---------+---------
---+
Help is required.
I run Mysql 4.0.13
Thanks
Patrizio


 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]