CODEWITHSUNDEEP
mysqldatabase
Kumar Soneta
Kumar Soneta

Reputation: 61

Looking for Mysql query

I have mysql table with following 3 fields:

- vouchercode: Unique KEY
- voucherstatus: 1 for unused voucher, 3 for used voucher
- partnerId

I want to display how many vouchers are used and unused w.r.t partnerId.

Example :- table data 
PartnerId voucherstaus  vouchercode(unique)
1          1
1          3 
1          1
2          3
2          3
2          1


Result:
PartnerId  usedvouchers unusedvouchers
1          1             2
2          2             1

Please help me with mysql query for same. Seems i have to use subquery and group by doesn't work. Thanks in advance

Upvotes: 0

Views: 55

Answers (2)

Suhel Meman
Suhel Meman

Reputation: 3852

select PartnerId, SUM(case when  voucher_status =1 then 1 else 0 end) as unused, sum(case when  voucher_status =3 then 1 else 0 end) as used from Voucher group by PartnerID;

sqlfiddle *demo*

Upvotes: 0

John Woo
John Woo

Reputation: 263803

SELECT  partnerID,
        SUM(CASE WHEN voucherstatus = 1 THEN 1 ELSE 0 END) unusedvouchers,
        SUM(CASE WHEN voucherstatus = 3 THEN 1 ELSE 0 END) usedvouchers 
FROM    data
GROUP   BY partnerID

Upvotes: 3

Related Questions