CODEWITHSUNDEEP
mysqlsql
Hassaan
Hassaan

Reputation: 328

Mysql two where condition on to total one column

I have a table suspended_bills where I want to get a sum of the total but I want the total in a single query.check this query

SELECT  date, (select sum(total) where type='bill') as bill_total, (select 
sum(total) where type='quotation') as quotation_total from suspended_bills 
group by YEAR(date),MONTH(date)

I know this is the wrong query but I want to know the solution to get the sum of bill and quotation in a single query.

Upvotes: 0

Views: 26

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

I think you want conditional aggregation:

select YEAR(date), MONTH(date), 
       sum(case when type = 'bill' then total else 0 end) as bill_total, 
       sum(case when type = 'quotation' then total else 0 end) as quotation_total
from suspended_bills 
group by YEAR(date), MONTH(date);

Upvotes: 2

Related Questions