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.
CodePudding user response：
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