CODEWITHSUNDEEP
mysql
cruim
cruim

Reputation: 319

Sum of sum in query

I have this 

    SELECT order_customFields.order_customFields_delivery_method,

    sum(case `order`.order_status when 'paid' then 1 else 0 end) paid,
    sum(case `order`.order_status when 'later' then 1 else 0 end) later,
  sum(case `order`.order_status when 'delivery-approved' then 1 else 0 end) deliveryapproved,
  sum(case `order`.order_status when 'problem' then 1 else 0 end) problem
FROM order_customFields 
  INNER JOIN `order` ON order_customFields.order_id = `order`.order_id
WHERE
  order_customFields.order_customFields_order_date >= '2016-12-01' AND
  order_customFields.order_customFields_order_date <= '2016-12-31' 
AND order_customFields.order_customFields_delivery_method is not null
GROUP BY    
   order_customFields.order_customFields_delivery_method

Look like enter image description here

How get another string in query, like 'Europe', in which the cells be summ of couriers(all 'eu')? Forexample Europe - paid 1471

Upvotes: 0

Views: 37

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

you could use union and a proper finter

SELECT order_customFields.order_customFields_delivery_method,
      sum(case `order`.order_status when 'paid' then 1 else 0 end) paid,
      sum(case `order`.order_status when 'later' then 1 else 0 end) later,
      sum(case `order`.order_status when 'delivery-approved' then 1 else 0 end) deliveryapproved,
      sum(case `order`.order_status when 'problem' then 1 else 0 end) problem
FROM order_customFields 
  INNER JOIN `order` ON order_customFields.order_id = `order`.order_id
WHERE
  order_customFields.order_customFields_order_date >= '2016-12-01' AND
  order_customFields.order_customFields_order_date <= '2016-12-31' 
AND order_customFields.order_customFields_delivery_method is not null
GROUP BY    
   order_customFields.order_customFields_delivery_method

UNION 


SELECT 'EUROPE',
      sum(case `order`.order_status when 'paid' then 1 else 0 end) paid,
      sum(case `order`.order_status when 'later' then 1 else 0 end) later,
      sum(case `order`.order_status when 'delivery-approved' then 1 else 0 end) deliveryapproved,
      sum(case `order`.order_status when 'problem' then 1 else 0 end) problem
FROM order_customFields 
  INNER JOIN `order` ON order_customFields.order_id = `order`.order_id
WHERE
  order_customFields.order_customFields_order_date >= '2016-12-01' AND
  order_customFields.order_customFields_order_date <= '2016-12-31' 
AND order_customFields.order_customFields_delivery_method is not null
AND substr(order_customFields.order_customFields_delivery_method,1,3) ='eu_'
GROUP BY    
   substr(order_customFields.order_customFields_delivery_method,1,3)

Upvotes: 1

Related Questions