0

I've got four MySQL tables: contacts, orders, ordered_products and products and I'm trying to write some reporting queries. Here's one of them:

SELECT t1.name, t1.contact_id, SUM(t1.prodSoldQty) AS contactSoldQty, SUM(t1.prodSoldVol) AS soldVol
FROM contacts
INNER JOIN
(
 SELECT 
    contacts.name,
        ordered_products.product_id,
        ordered_products.contact_id,
        SUM(ordered_products.quantity) AS prodSoldQty,
        (products.price * SUM(ordered_products.quantity)) AS prodSoldVol
 FROM ordered_products
 INNER JOIN contacts ON contacts.id = ordered_products.contact_id
 INNER JOIN products ON products.id = ordered_products.product_id
 INNER JOIN orders ON orders.id = ordered_products.order_id
 GROUP BY ordered_products.product_id , ordered_products.contact_id
 ORDER BY ordered_products.product_id
) AS t1 ON t1.contact_id = contacts.id
GROUP BY contacts.id
ORDER BY t1.name;

Everything works as expected, albeit if I add (and I need to add) another [LEFT|INNER] JOIN (for example: INNER JOIN orders ON orders.contact_id = contacts.id) the rows SUM totals got messed up (they seems to duplicate, triplicate ... accordingly to the number of the orders). I guess the problem might be the multiple grouping in the first join derived table. Yet it eludes me. Here you can find a slightly variant of the above query. Even this tiny modification is able to trigger the 'issue':

SELECT t1.name, t1.contact_id,
SUM(t1.prodSoldQty) AS contactSoldQty,
SUM(t1.prodSoldVol) AS soldVol
FROM contacts
INNER JOIN
(
 SELECT 
    contacts.name,
        ordered_products.product_id,
        ordered_products.contact_id,
        SUM(ordered_products.quantity) AS prodSoldQty,
        (products.price * SUM(ordered_products.quantity)) AS prodSoldVol
 FROM ordered_products
 INNER JOIN contacts ON contacts.id = ordered_products.contact_id
 INNER JOIN products ON products.id = ordered_products.product_id
 INNER JOIN orders ON orders.id = ordered_products.order_id
 GROUP BY ordered_products.product_id , ordered_products.contact_id
 ORDER BY ordered_products.product_id
) AS t1 ON t1.contact_id = contacts.id
INNER JOIN orders ON orders.contact_id = contacts.id
WHERE orders.order_state_id != '111'
GROUP BY contacts.id
ORDER BY t1.name;

The above path is a dead end. With the help from @Gordon Linoff I was able to find a better way. One of the reporting goal was to calculate total product cost amount & total product selling amount on a customer/contact base. I was focused on doing that with two separate join. It seems that one will suffice (avoidig including several times nearly the same tables):

SELECT t1.name, t1.contact_id,
SUM(t1.prodSellQty) AS sellQty,
SUM(t1.prodCostVol) AS costAmount,
SUM(t1.prodSellVol) AS sellAmount
FROM contacts
INNER JOIN
(
 SELECT 
    contacts.name,
        ordered_products.contact_id,
        SUM(ordered_products.quantity) AS prodSellQty,
        SUM(products.price * ordered_products.quantity) AS prodCostVol,
        SUM(ordered_products.price * ordered_products.quantity) AS prodSellVol
 FROM ordered_products
 INNER JOIN contacts ON contacts.id = ordered_products.contact_id
 INNER JOIN products ON products.id = ordered_products.product_id
 INNER JOIN orders ON orders.id = ordered_products.order_id
 GROUP BY ordered_products.product_id , ordered_products.contact_id
 ORDER BY ordered_products.product_id
) AS t1 ON t1.contact_id = contacts.id
GROUP BY contacts.id
ORDER BY contacts.name;

Thanks, Luca

Luca Anceschi
  • 2,257
  • 3
  • 19
  • 24
  • Try adding just the join condition `orders.contact_id = contacts.id` to the orders table in the inner query. – user2989408 Jan 28 '14 at 17:11
  • do you want some extra columns from orders table? if records are duplicate or triplicate than you can use distinct to remove duplicates. – nitish Jan 28 '14 at 17:22
  • You are joining along two different dimensions, contacts and products. This is causing a cartesian product for a given order when there is more than one contact and more than one order. – Gordon Linoff Jan 28 '14 at 17:24
  • @nitish the 'issue' presents itself only if I add another join to the main contacts table. The quoted query returns me correct values. – Luca Anceschi Jan 28 '14 at 17:44
  • @Gordon Linoff: yes, you are right albeit the quoted query (that with the outer grouping by contact.id) works as expected. The 'issue' shows up if I add for instance another join in the outer query. Any clue? – Luca Anceschi Jan 28 '14 at 17:51
  • @Luca you are getting all columns in your sub query than why are you adding join between sub query and contract table. you can easily get same output by removing "ordered_products.product_id" column form select and group by statements. And please tell you requirement so that I can help. – nitish Jan 28 '14 at 17:54
  • @LucaAnceschi . . . Can you post your actual query with the additional orders table in it? I suspect the problem is a simple one of misused table aliases. – Gordon Linoff Jan 28 '14 at 17:56
  • @GordonLinoff: I added the missing part for completeness. Even this slight modification triggers rows sum totals. – Luca Anceschi Jan 28 '14 at 21:20
  • 1
    @LucaAnceschi . . . Of course. Contacts are on multiple orders, so the `join` multiplies rows. What are you trying to do with the `where` clause? Should it go in the subquery? – Gordon Linoff Jan 28 '14 at 21:23
  • @nitish: what is it "group by statements"? Do you mean fields/columns? What do you mean with "... tell you requirement ..."? Thx btw – Luca Anceschi Jan 28 '14 at 21:27
  • @GordonLinoff: you're right! I realize I was following the wrong path. I was trying to calculate total sold amount & total purchase amount per contact/customer with two separate joins which share nearly the same tables. Unpredictable behaviour most probably. Can you pinpoint me some online resources over this topic? Thx – Luca Anceschi Jan 28 '14 at 21:43

0 Answers0