Prashanth Prashanth - 4 months ago 10
MySQL Question

Count twice if certain condition satisfies in mysql

I have a scenario where I need to display total number of attendees of an event. With the help of registration form I have already captured the details of people who are attending and my table looks like below.




ID     | NAME     | PHONE_NUMBER     | IS_LIFE_PARTNER_ATTEDNING





1      | ABC      | 1234567890       | N                          





2      | PQR      | 1234567891       | Y                          





3      | XYZ      | 1234567892       | N                          


I can easily display number of registrations by using count(id). But while displaying number of attendees I have to consider as two attendees if registrant is coming with his/her partner. (identified by IS_LIFE_PARTNER_ATTEDNING column)

So, in the above case, the number of registrants are 3, but number of attendees are 4, because "PQR" is coming with his/her life partner.

How can we do this in mysql query?

Answer

Use SUM with CASE

SELECT 
Name,
SUM(CASE WHEN IS_LIFE_PARTNER_ATTEDNING='y' THEN 2 ELSE 1 END ) AS'Attendes'
FROM
table
GROUP by name