CODEWITHSUNDEEP
mysqlsql
Smith
Smith

Reputation: 5961

Select and summarize data from three tables

i have three tables

customer

id | name
 1 | john

orders

id | customer_id | date
1  | 1           | 2013-01-01
2  | 1           | 2013-02-01
3  | 2           | 2013-03-01

order_details

id | order_id  | qty | cost
1  | 1         | 2   | 10
2  | 1         | 5   | 10
3  | 2         | 2   | 10
4  | 2         | 2   | 15
5  | 3         | 3   | 15
6  | 3         | 3   | 15

i need to select data so i can get the output for each order_id the summary of the order sample output. I will query the database with a specific customer id

output

date       | amount | qty | order_id
2013-01-01 | 70     |  7  |  1
2013-02-01 | 50     |  4  |  2

this is what i tried

SELECT 
orders.id, orders.date, 
SUM(order_details.qty * order_details.cost) AS amount, 
SUM(order_details.qty) AS qty
FROM  orders 
LEFT OUTER JOIN order_details ON  order_details.order_id=orders.id AND orders.customer_id = 1 
GROUP BY orders.date

but this returns the same rows for all customers, only that the qty and cost dont hav values

Upvotes: 0

Views: 81

Answers (3)

axiopisty
axiopisty

Reputation: 5136

NOTE: In the following query, it is assumed that the dates are stored in the database as a string in the format specified in the OP. If they are actually stored as some type of date with time then you'll want to modify this query such that the time is truncated from the date so the date represents the whole day. You can use the date or date_format functions. But then you'll need to make sure that you modify the query appropriately so the group by and select clauses still work. I added this modification as comments inside the query.

select
  o.date  -- or date(o.date) as date
  , sum(odtc.total_cost) as amount
  , sum(odtc.qty) as qty
  , o.order_id
from
  orders o
  inner join (
    select
      od.id
      , od.order_id
      , od.qty
      , od.qty * od.cost as total_cost
    from
      order_details od
      inner join orders _o on _o.id = od.order_id
    where
      _o.customer_id = :customer_id
    group by
      od.id
      , od.order_id
      , od.qty
      , od.cost
  ) odtc on odtc.order_id = o.id
where
  o.customer_id = :customer_id
group by 
  o.date -- or date(o.date)
  , o.order_id
;

Upvotes: 1

Mihai
Mihai

Reputation: 26784

Maybe

    SELECT 
orders.id, orders.date, 
SUM(order_details.qty * order_details.cost) AS amount, 
SUM(order_details.qty) AS qty
FROM  orders  
LEFT JOIN order_details ON  order_details.order_id=orders.id 
AND orders.customer_id = 1 
GROUP BY orders.date
HAVING amount is not null AND qty is not null

SQL Fiddle

Upvotes: 1

Digital Chris
Digital Chris

Reputation: 6202

I don't think you want an outer join just a simple inner join on all 3 tables:

FROM orders, order_details, customer
WHERE orders.customer_id=customer.id
AND order_details.order_id=orders.id

Upvotes: 0

Related Questions