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?

Solution 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.

Solution 2

Try this with SUM() and without CASE

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

See Fiddle Demo

Solution 3

Try this:

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

Check the SQL FIDDLE DEMO

OUTPUT

| COUNT(*) | HAS_VOTED |
|----------|-----------|
|        3 |         1 |

Solution 4

You are inadvertently using a MySQL feature here: You aggregate your results to get only one result record showing the number of matches (aggregate function COUNT). But you also show the user (or rather an expression built on it) in your result line (without any aggregate function). So the question is: Which user? Another dbms would have given you an error, asking you to either state the user in a GROUP BY or aggregate users. MySQL instead picks a random user.

What you want to do here is aggregate users (or rather have your expression aggregated). Use SUM to sum all votes the user has given on the requested content:

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

Solution 5

You need sum of votes.

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

View more solutions

Comments

[Thorsten Kettner]

This is a nice example of using the MySQL feature to retrieve boolean expression results. A very good solution to the problem given. Your answer would have been great, had you not only given a working solution, but explained daker's mistake. My vote though, for the best solution in my opinion.