CODEWITHSUNDEEP
mysqlsql
Awais Khan
Awais Khan

Reputation: 175

How to get different status count by product id

I have got the result form a complex query below

SELECT o_items.sku,
       o_items.name        AS 'title',
       o_items.qty_ordered AS 'quantity',       
       s_orders.base_amount_paid AS 'paid/unpaid'
  FROM sales_order_payment s_orders
 INNER JOIN (SELECT s.sku, s.name, s.qty_ordered, s.order_id
               FROM sales_order_item s             
              INNER JOIN (SELECT p.entity_id
                           FROM catalog_product_entity AS p                         
                          INNER JOIN catalog_product_entity_int AS a
                             ON p.row_id = a.row_id
                          WHERE VALUE >= 0
                            AND                               
                                a.attribute_id =
                                (SELECT attribute_id
                                   FROM eav_attribute                                 
                                  WHERE attribute_code = 'is_darkhorse')) as q
                 ON s.product_id = q.entity_id             
              WHERE s.created_at BETWEEN '2019-01-14' AND '2019-01-16') o_items
          ON
             s_orders.parent_id = o_items.order_id

enter image description here this is the order data those have been paid or not paid yet. Amount is representing paid and Null representing unpaid status

I am trying to generate below result but couldn't succeed and need help. Actually this result is showing how may quantity of a product has been paid and how many not paid yet. This would be result of above fetched data. enter image description here

Please guide me how can i proceed to achieve these result.

Upvotes: 0

Views: 175

Answers (2)

Muhammad Waheed
Muhammad Waheed

Reputation: 1088

Use this. ... represent existing code.

select .... , sum(case  when s_orders.base_amount_paid  is not null 
                        then o_items.qty_ordered
                        else 0 
                  end) as paid, 
              sum(case  when s_orders.base_amount_paid  is null 
                        then o_items.qty_ordered 
                        else 0 
                  end) as unpaid
From .......

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65373

You can use if and ifnull functions together(presuming you're using mysql as DBMS)

and GROUP BY expression 

SELECT c.sku, c.name, 
       sum(if(ifnull(base_amount_paid,0)=0,0,1)) as paid,
            sum(if(ifnull(qty_ordered,0)=0,0,1)) as unpaid
  FROM catalog_prod_ent_derived c
 GROUP BY c.sku, c.name

where catalog_prod_ent_derived represents your whole query as a subquery.

Upvotes: 0

Related Questions