Search Posts

left join count ignore left table column from counting

how to – left join count ignore left table column from counting

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

Source: Stackoverflow

Leave a Reply

Your email address will not be published. Required fields are marked *