Yasin Yaqoobi Yasin Yaqoobi - 6 months ago 10
SQL Question

Select change column value if in list

I am trying to query my table to count the number of votes and if the voting method is in list

['C', 'M', 'S', 'L', 'T', 'V', 'B', 'E']
then count it as one and replace the voting_method to 'L'.

Right now I have the following query which returns the right results but doesn't take care of the duplicates.

select `election_lbl`, `voting_method`, count(*) as numVotes
from `gen2014` group by `election_lbl`, `voting_method` order by `election_lbl` asc

election_lbl voting_method numVotes
2014-09-04 M 1
2014-09-05 M 2
2014-09-05 S 1
2014-09-08 C 16
2014-09-08 M 5
2014-09-08 S 9
2014-09-09 10 5
2014-09-09 C 46
2014-09-09 M 4
2014-09-09 S 5
2014-09-10 C 92
2014-0g-10 M 3
2014-09-10 S 7
2014-09-11 C 96
2014-09-11 M 3
2014-09-11 S 2
2014-09-12 C 104
2014-09-12 M 10
2014-09-12 S 3
2014-09-15 C 243
2014-09-15 M 18
2014-09-15 S 3
2014-09-16 10 1
2014-09-16 C 161
2014-09-16 M 4
2014-09-16 S 3
2014-09-17 C 157
2014-09-17 M 5
2014-09-17 S 12


You can see that for 2014-09-05 I have two voting_method M and S both of which is in the list. I want the ideal result to remove the duplicate date field if the values are in the list. So it would be 2014-09-05 'L' 3. I don't want the vote for that date to disappear so the results should count them as one.

Changed the query to this but mysql says wrong syntax.

select `election_lbl`, `voting_method`, count(*) as numVotes from `gen2014`
(case `voting_method` when in ('C', 'M', 'S', 'L', 'T', 'V', 'B', 'E')
then 'L' END) group by `election_lbl`, `voting_method` order by `election_lbl` asc


Table Schema

CREATE TABLE `gen2014` (
`voting_method` varchar(255) DEFAULT NULL,
`election_lbl` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer
SELECT election_lbl
     , CASE WHEN voting_method IN ('C','M','S','L','T','V','B','E')
            THEN 'L' 
            ELSE voting_method END my_voting_method
     , COUNT(*) 
  FROM my_table 
 GROUP 
    BY my_voting_method    -- or vice
     , election_lbl;       -- versa