CODEWITHSUNDEEP
mysqljoinreport
Amey
Amey

Reputation: 8548

Multiple inner joins to get a complex report, not working

For the schema below, I need to get this report

This is what I have 

select c.name, sr.name, count(o.order_id)
from contact c 
INNER JOIN accounts a
ON c.account_id=a.account_id
INNER JOIN sales_reps sr 
ON a.sales_rep_id =sr.sales_rep_id
INNER JOIN orders o
ON a.account_id =o.account_id
where o.order_id in (
    select SUM(oi.quantity*p.price) from 
    order_items oi INNER JOIN parts p
    on oi.part_id =p.part_id
)
group by a.account_id, c.name

But this does not give any results.

Please help.

Upvotes: 3

Views: 97

Answers (3)

DRapp
DRapp

Reputation: 48149

It won't give results as your WHERE ... IN SELECT is based on a query returning a sum() value which will not equal a key (most likely), or incorrect at best... and since you are dealing with a quantity and price which will have decimal precision (typically), you won't even get that to match even LESS likely...

I would swap the query around to pre-qualify the orders within a given date in question and sum that... THEN join to rest...

select
         c.name, 
         sr.name,
         PreQualified.NumberOrders,
         PreQualified.OrderTotal
      from
         ( select 
                 o.Account_ID,
                 count( distinct o.order_id ) as NumberOrders,
                 sum( oi.quantity * p.price ) as OrderTotal
              from 
                 orders o
                    join order_items oi
                       on o.order_id = oi.order_id
                       join parts p
                          on oi.part_id = p.part_id
              where
                 o.Delivery_Date >= CURDATE()
              group by
                 o.Account_ID ) as PreQualified
            JOIN Accounts a
               on PreQualified.Account_ID = a.Account_ID
               Join Contact C
                  on a.Account_ID = c.Account_ID
            JOIN Sales_Reps sr
               ON a.sales_rep_id = sr.sales_rep_id

Upvotes: 3

xdazz
xdazz

Reputation: 160883

Your where condition is not right, how should be a order_id equal a sum?

Try the below:

select 
    c.name, sr.name, COUNT(o.order_id), SUM(op.order_total)
FROM 
    contact c 
INNER JOIN 
    accounts a ON c.account_id = a.account_id
INNER JOIN 
    sales_reps sr ON a.sales_rep_id = sr.sales_rep_id
INNER JOIN 
    orders o ON a.account_id = o.account_id
INNER JOIN 
  (SELECT 
      oi.order_id, SUM(oi.quantity * p.price) AS order_total
  FROM 
      order_items oi 
  INNER JOIN 
      parts p ON oi.part_id = p.part_id 
  GROUP BY 
      oi.order_id
  ) op ON o.order_id = op.order_id
WHERE o.delivery_data >= CURDATE()
GROUP by c.contact_id

Upvotes: 3

Adrian Cornish
Adrian Cornish

Reputation: 23876

If you want to count records use

count(*)

Instead of

count(o.order_id)

Upvotes: 0

Related Questions