R: sum() problems - I don't understand
Exactly that's the problem! how can I rewrite the query syntax avoiding the multiplication? Thanks Patrizio -Messaggio originale- Da: Keith C. Ivey [mailto:[EMAIL PROTECTED] Inviato: giovedì 26 giugno 2003 22.15 A: [EMAIL PROTECTED] Oggetto: Re: sum() problems - I don't understand On 26 Jun 2003 at 15:07, gerald_clark wrote: > The second query contains a join and may have many times more rows in > the result set. [snip] > >++-+--++--+ > >| date1 | sum1| sum2 | cod| fromprev | > >++-+--++--+ > >| 29-05-2003 | 436.498 | 10 | SC0001GP | 2666 | > >++-+--++--+ [snip] > >| date1 | sum1 | sum2 | cod | fromprev | numprev | date2 > >| > >++--+--+-+--+-+-- > >+--- > >---+ > >| 29-05-2003 | 3491.984 | 80 | SC0001GP| 2666 |2666 | > >28-05-2003 | > >++--+--+-+--+-+-- In fact it appears that for this query there are 8 rows in 'carello' corresponding to each row in 'ordini', so the sums are multiplied by 8. -- Keith C. Ivey <[EMAIL PROTECTED]> Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sum() problems - I don't understand
What is the purpose of the join? I think I know what you are trying to do so make the first query a sub query and join the subquery to the carello table. Regards, Jake Johnson [EMAIL PROTECTED] -- Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Car Audio, and Performance Parts. On Thu, 26 Jun 2003, gerald_clark wrote: > 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 > > FROMordini > > WHERE ordini.numordine = 2302 > > GROUP BYordini.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 > >WHEREordini.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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sum() problems - I don't understand
On 26 Jun 2003 at 15:07, gerald_clark wrote: > The second query contains a join and may have many times more rows in > the result set. [snip] > >++-+--++--+ > >| date1 | sum1| sum2 | cod| fromprev | > >++-+--++--+ > >| 29-05-2003 | 436.498 | 10 | SC0001GP | 2666 | > >++-+--++--+ [snip] > >| date1 | sum1 | sum2 | cod | fromprev | numprev | date2 > >| > >++--+--+-+--+-+-- > >+--- > >---+ > >| 29-05-2003 | 3491.984 | 80 | SC0001GP| 2666 |2666 | > >28-05-2003 | > >++--+--+-+--+-+-- In fact it appears that for this query there are 8 rows in 'carello' corresponding to each row in 'ordini', so the sums are multiplied by 8. -- Keith C. Ivey <[EMAIL PROTECTED]> Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sum() problems - I don't understand
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 FROMordini WHERE ordini.numordine = 2302 GROUP BYordini.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 WHEREordini.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]
sum() problems - I don't understand
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 FROMordini WHERE ordini.numordine = 2302 GROUP BYordini.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 WHEREordini.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]
