SELECT CASE, COUNT(*)

Question

I want to select the number of users that has marked some content as favorite and also return if the current user has "voted" or not. My table looks like this

CREATE TABLE IF NOT EXISTS `favorites` (
`user` int(11) NOT NULL DEFAULT '0',
`content` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY  (`user`,`content`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;

Say I have 3 rows containing

INSERT INTO `favorites` (`user`, `content`) VALUES
(11, 26977),
(22, 26977),
(33, 26977);

Using this

SELECT COUNT(*), CASE
        WHEN user='22'
           THEN 1
           ELSE 0
   END as has_voted
FROM favorites WHERE content = '26977'

I expect to get has_voted=1 and COUNT(*)=3 but

I get has_voted=0 and COUNT(*)=3. Why is that? How to fix it?

Answer 1

This is because you mixed aggregated and non-aggregated expressions in a single SELECT. Aggregated expressions work on many rows; non-aggregated expressions work on a single row. An aggregated (i.e. COUNT(*)) and a non-aggregated (i.e. CASE) expressions should appear in the same SELECT when you have a GROUP BY, which does not make sense in your situation.

You can fix your query by aggregating the second expression - i.e. adding a SUM around it, like this:

SELECT
    COUNT(*) AS FavoriteCount
,   SUM(CASE WHEN user=22 THEN 1 ELSE 0 END) as has_voted
FROM favorites
WHERE content = 26977

Now both expressions are aggregated, so you should get the expected results.

Answer 2

Try this with SUM() and without CASE

SELECT 
  COUNT(*),
  SUM(USER = '22') AS has_voted 
FROM
  favorites 
WHERE content = '26977' 

See Fiddle Demo