How to use count in sql based on a IF condition

From this table

groupId flag flagValue
1 0 500
2 0 100
1 1 10
2 1 50
3 0 100
1 1 200
3 1 1000
2 1 50

I need this result

groupId flag1 flag0 valFlag1 valFlag0 totalFlags
1 2 1 210 500 3
2 2 1 100 100 3
3 1 1 1000 100 2

where

• flag1 is number of times flag is 1 for a particular group
• flag0 is number of times flag is 0 for a particular group
• valFlag1 is sum of flagVal when flag is 1
• valFlag0 is sum of flagVal when flag is 0
• totalFlags is sum of total flags associated with a group

I am stuck as to how to actually count values based on an IF condition.
Anyhelp is appreciated. Thanks.

>Solution :

I have used a table named `group_table` with your values
Try using this:

``````SELECT
g.`groupId`,
SUM(g.`flag`=1 ) AS flag1,
SUM(g.`flag`=0) AS flag0,
SUM(CASE WHEN g.`flag`=1 THEN g.`flagValue` ELSE 0 END) AS valFalg1,
SUM(CASE WHEN g.`flag`=0 THEN g.`flagValue` ELSE 0 END) AS valFalg0,
COUNT(*) AS totalFlags
FROM
`group_table` g
GROUP BY g.`groupId`
``````

If you have to use the `IF`,

``````SELECT
g.`groupId`,
IF(g.`flag`=1,1,0 ) AS flag1,
IF(g.`flag`=0,1,0) AS flag0,
SUM(IF(g.`flag`=1,g.`flagValue`,0 )) AS valFalg1,
SUM(IF(g.`flag`=0,g.`flagValue`,0 )) AS valFalg0,
COUNT(*) AS totalFlags
FROM
`group_table` g
GROUP BY g.`groupId`
``````

They’ll produce the same result