CODEWITHSUNDEEP
mysqlsql
HandyManDan
HandyManDan

Reputation: 478

MySQL sum on condition does not work in a view

In MySQL (v5.5), the value of numNotCast, numInFavor is always 0 when selecting all rows of the view. If the select statement is executed alone (not in the view), it works as expected returning the correct count of the the number of rows in the vote column equal to value 'notcast' and 'infavor'. 

    CREATE OR REPLACE
    VIEW `Stats` AS
    select 
        sum(case when `p`.`vote` = 'notcast' then 1 else 0 end) AS `numNotCast`,
        sum(case when `p`.`vote` = 'infavor' then 1 else 0 end) AS `numInFavor`
    from
        (`Debate` `d`
        join `Participant` `p` ON ((`d`.`debateId` = `p`.`debateId`)))
    group by `d`.`debateId`

Is this a limitation on MySQL views? How do you accomplish this conditional summing function in the view?

Upvotes: 0

Views: 87

Answers (2)

georstef
georstef

Reputation: 1388

Then your statement is correct as SQLFiddle confirms, so your problem is probably somewhere else

CREATE OR REPLACE VIEW Stats AS    
select 
  Debate.debateId,
  sum(case when Participant.vote='notcast' then 1 else 0 end) as numNotCast,
  sum(case when Participant.vote='infavor' then 1 else 0 end) as numInFavor
from
  Debate
inner join 
  Participant on Participant.debateId = Debate.debateId
group by
  Debate.debateId

Updated after the clarification

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269633

There are many limitations in MySQL views, but I don't think this is one of them.

I think it is strange that the name of the view is in single quotes. That might be allowed, but the view may not be doing what you want. Try this:

CREATE OR REPLACE VIEW `Stats` AS
    select sum(case when `p`.`vote` = 'notcast' then 1 else 0 end) AS `numNotCast`
    from `Debate` `d` join
         `Participant` `p`
         ON `d`.`debateId` = `p`.`debateId`
    group by `d`.`debateId`;

By the way, in MySQL, you can simplify the select to:

select sum(p.vote = 'notcast') as numNotCast

Upvotes: 1

Related Questions