1

I have 3 tables as shown in the link sqlfiddle When I take the sum with two tables I get good result sqlfiddle

but when I add 3rd table and it's query I get unexpected result as this

http://sqlfiddle.com/#!2/f9858a/12

How can I resolve this problem pls ?

  • can you explain a little bit what is unexpected? – Riad Dec 10 '14 at 17:58
  • The sums are changing as shown in two sqlfiddle. For exemple quantity of Beurre which must 500 becomes 1000 in second query all the sums are doubled – Ismail Gunes Dec 10 '14 at 18:12
1

When you do the aggregation without ingredient, then LEFT JOIN result to ingredient you get this

mysql> SELECT A.*,ig.qty FROM
    -> (SELECT dm.id,dm.nom,
    -> SUM(IF(dm.id=sd.matid, quantite,0)) as thisqty,
    -> SUM(IF(dm.id=sd.matid, sd.montant * (1+sd.tvaval/100),0)) AS thissum
    -> FROM stockdata AS sd
    -> JOIN defmatiere as dm ON dm.id=sd.matid
    -> WHERE dm.type=1 GROUP BY dm.nom ORDER BY dm.id) A
    -> LEFT JOIN ingredient as ig ON ig.matid=A.id;
+----+----------+---------+--------------------+------+
| id | nom      | thisqty | thissum            | qty  |
+----+----------+---------+--------------------+------+
|  1 | Amande   |   19470 | 204.74464999999998 |  600 |
|  1 | Amande   |   19470 | 204.74464999999998 |  200 |
|  2 | Chocolat |    7920 |              62.97 |  100 |
|  2 | Chocolat |    7920 |              62.97 |  200 |
|  4 | Beurre   |     500 |               2.48 |   60 |
|  4 | Beurre   |     500 |               2.48 |   60 |
+----+----------+---------+--------------------+------+
6 rows in set (0.00 sec)

mysql>

Perhaps doing an aggregation after the INNER JOIN would help

mysql> SELECT A.*,SUM(ig.qty) igqty FROM
    -> (SELECT dm.id,dm.nom,
    -> SUM(IF(dm.id=sd.matid, quantite,0)) as thisqty,
    -> SUM(IF(dm.id=sd.matid, sd.montant * (1+sd.tvaval/100),0)) AS thissum
    -> FROM stockdata AS sd
    -> JOIN defmatiere as dm ON dm.id=sd.matid
    -> WHERE dm.type=1 GROUP BY dm.nom ORDER BY dm.id) A
    -> LEFT JOIN ingredient as ig ON ig.matid=A.id
    -> GROUP BY A.id,A.nom,A.thisqty,A.thissum;
+----+----------+---------+--------------------+-------+
| id | nom      | thisqty | thissum            | igqty |
+----+----------+---------+--------------------+-------+
|  1 | Amande   |   19470 | 204.74464999999998 |   800 |
|  2 | Chocolat |    7920 |              62.97 |   300 |
|  4 | Beurre   |     500 |               2.48 |   120 |
+----+----------+---------+--------------------+-------+
3 rows in set (0.00 sec)

mysql>
  • Thanks I am sorry to not to vote because my reputation is insufficient. I should ask this in stackoverflow. I'll keep in mind – Ismail Gunes Dec 10 '14 at 19:16
  • You should be able to upvote now. Your rep is 23. – RolandoMySQLDBA Dec 10 '14 at 19:27
  • If this answered your question, you can mark it acepted by clicking on the checkmark on my question. – RolandoMySQLDBA Dec 10 '14 at 21:04
  • This was exact answer which resolved my problem. However I forgot to add date filter by sd.st_date which exists in sqlfiddle. I did not find where to put this filter any suggestion ? It's like WHERE "sd.st_date>=" +1stdate + " AND sd.st_date<=" + 2nddate. – Ismail Gunes Dec 10 '14 at 21:38
  • Between WHERE dm.type=1 and GROUP BY dm.nom ORDER BY dm.id – RolandoMySQLDBA Dec 10 '14 at 21:43
0

However my ingredient table (can be seen in the slqfiddle) was not suitable for this query. Finally I added a date column in ingredient table so i can retreive with date condition as expected.

The result is in the link

sqlfiddle

Any idea without addition of date column will be appreciated.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.