CODEWITHSUNDEEP
mysqlsql
stand
stand

Reputation: 139

Sum of columns in query

I have this query 

`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
FROM order_customFields 
  INNER JOIN `order` ON order_customFields.order_id = `order`.order_id

where `order`.order_createdAt >= date_sub(date_sub(curdate(), interval day(curdate()) - 1 day), interval 1 month) 
and `order`.order_createdAt < date_sub(curdate(), interval day(curdate()) - 1 day)
AND order_customFields.order_customFields_delivery_method in('a','b','c','d','e')
GROUP BY    
   order_customFields.order_customFields_delivery_method`

How can I get total sum for colums paid and later in this query? Like this example

Upvotes: 0

Views: 48

Answers (4)

Gurwinder Singh
Gurwinder Singh

Reputation: 39467

Just use IN if you want to find the sum of the two columns

sum(case when `order`.order_status in ('paid', 'later') then 1 end)

Upvotes: 3

Sonali
Sonali

Reputation: 67

Try this

SELECT order_customFields.order_customFields_delivery_method,
sum(case `order`.order_status when 'paid' then 1 else 0 end)+
sum(case `order`.order_status when 'later' then 1 else 0 end)
FROM order_customFields 
INNER JOIN `order` ON order_customFields.order_id = `order`.order_id
where `order`.order_createdAt >= date_sub(date_sub(curdate(), interval 
day(curdate()) - 1 day), interval 1 month) 
and `order`.order_createdAt < date_sub(curdate(), interval day(curdate()) - 1 day)
AND order_customFields.order_customFields_delivery_method in('a','b','c','d','e')
GROUP BY    
order_customFields.order_customFields_delivery_method`

Upvotes: 1

Srikanth Balaji
Srikanth Balaji

Reputation: 2718

You can just do an addition using addition operator - "+"

SELECT order_customFields.order_customFields_delivery_method,

    sum(sum(case `order`.order_status when 'paid' then 1 else 0 end) +
    sum(case `order`.order_status when 'later' then 1 else 0 end)) total
FROM order_customFields 
  INNER JOIN `order` ON order_customFields.order_id = `order`.order_id

where `order`.order_createdAt >= date_sub(date_sub(curdate(), interval day(curdate()) - 1 day), interval 1 month) 
and `order`.order_createdAt < date_sub(curdate(), interval day(curdate()) - 1 day)
AND order_customFields.order_customFields_delivery_method in('a','b','c','d','e')
GROUP BY    
   order_customFields.order_customFields_delivery_method

Please refer the MYSQL Documentation for more details - Arithmetic Operations - MYSQL

Upvotes: 1

krishn Patel
krishn Patel

Reputation: 2599

you can just put + and you will have total

sum(case `order`.order_status when 'paid' then 1 else 0 end)+
    sum(case `order`.order_status when 'later' then 1 else 0 end) total

Upvotes: 1

Related Questions