Select change column value if in list

Question

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;

`in` is basically an operator, the proper syntax should be `case when voting method in .... then ...`; though I can't think of any reason the query should need to be so complicated (unless you also want the numVotes for methods not in the list as well). — Uueerdo, May 17 '16 at 16:31
I actually do need the numVotes of the methods not in the list. The goal is to count the ones in the list as 'L' and leave the rest as is. — Yasin Yaqoobi, May 17 '16 at 16:44

score 1 · Answer 1 · answered May 17 '16 at 16:30

1

If you just want the total votes using those methods for each date, listed as method 'L', then do not include method in the group by, and have the SELECT select 'L' as voting_method

select `election_lbl`, 'L' AS `voting_method`, count(*) as numVotes 
from `gen2014` 
where voting_method IN ('C', 'M', 'S', 'L', 'T', 'V', 'B', 'E')
group by `election_lbl`
order by `election_lbl` asc

answered May 17 '16 at 16:30

Uueerdo

15,723
1
16
21

I need the numVotes of the methods not in the list too. The goal is to count the ones in the list as 'L' and leave the rest as is. – Yasin Yaqoobi May 17 '16 at 16:44
@YasinYaqoobi looks like Strawberry's answer is the way to go then. – Uueerdo May 17 '16 at 16:45
Thank you. Maybe you can solve this ? http://stackoverflow.com/questions/37104350/wordnet-mysql-synonyms-and-samples – Yasin Yaqoobi May 17 '16 at 16:51

score 1 · Accepted Answer · answered May 17 '16 at 16:41

1

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

answered May 17 '16 at 16:41

Strawberry

33,750
13
40
57

Thank you. That did solved it. I am stuck with another mysql problem as well. maybe you can solve it. http://stackoverflow.com/questions/37104350/wordnet-mysql-synonyms-and-samples – Yasin Yaqoobi May 17 '16 at 16:51

Walter_Ritzel · Answer 3 · 2016-05-17T17:03:12.343

0

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

edited May 17 '16 at 17:03

answered May 17 '16 at 16:29

Walter_Ritzel

1,387
1
12
16

.Got this error. Every derived table must have its own alias. – Yasin Yaqoobi May 17 '16 at 16:42
I have added the alias. – Walter_Ritzel May 17 '16 at 17:03

Select change column value if in list

3 Answers3