左连接计数忽略左表列从计数

Question

I have two tables ...first has order_id, order_status, and user_id .... second table has order_id ,and product_id, product_quantity like below

1st table

    order_id | order_status | user_id
    ----------------------------
    1        | 5            | 2
    2        | 1            | 1
    3        | 5            | 1
    4        | 5            | 1

2nd Table

order_id | product_id | quantity
----------------------------
1        | 200        | 4
2        | 201        | 2
2        | 200        | 1
2        | 207        | 4
3        | 201        | 1
3        | 200        | 6
4        | 201        | 8

I want to get

    user_id | Total_orders | quantity
    ----------------------------
    1       | 2            | 15
    2       | 1            | 4

get user_id where order_status = 5 and sum(quantity) group by user_id

MY TRY

SELECT h.user_id
     , COUNT(IF(h.order_status = 5,1,0)) AS total_orders
     , SUM(o.quantity) AS quantity 
  FROM table1 h 
  LEFT 
  JOIN table2 o  
    ON o.order_id = h.order_id 
 WHERE h.order_status = 5 
 GROUP 
    BY h.user_id

But, it gives me result counting all instance of order_id in left table like ... total orders of user_id 1 with status 5 are 2 but my query return 3 as count (order_id) bcz there are 3 instance of passed orders by user id 1.

Any suggestions or solutions ...i am stuck since long :(

thanks

Answer 1

You need to count the number of distinct occurrences of order_id:

SELECT h.user_id, 
       COUNT(DISTINCT h.order_id) AS total_orders, 
       SUM(o.quantity) AS quantity 
FROM table1 h 
LEFT JOIN table2 o ON o.order_id=h.order_id 
WHERE h.order_status = 5 
GROUP BY h.user_id

Also, using IF function inside COUNT like this:

COUNT(IF(h.order_status = 5,1,0)) AS total_orders

makes no sense since h.order_status is always equal to 5, due to the WHERE clause:

WHERE h.order_status = 5

Demo here