SELECT CASE, COUNT (*)

Question

I want to select the number of users who marked some content as favorites, 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 ;

Let's say I have 3 lines 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 receive has_voted=1and COUNT(*)=3but

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

Answer 1

, SELECT. ; . (.. COUNT(*)) (.. CASE) SELECT, GROUP BY, .

, , .. SUM, :

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

, .

Answer 2

SUM() CASE

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

.

Answer 3

:

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

SQL FIDDLE DEMO

OUTPUT

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

Answer 4

.

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

Answer 5

MySQL : , , ( COUNT). (, , , ) ( - ). , : ? dbms , GROUP BY . MySQL .

, , (, , ). , , :

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

Answer 6

You forgot to wrap the operator CASEinside the aggregate function. In this case, it has_votedwill contain unexpected results, since you are actually performing a "partial group by." Here is what you need to do:

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

Or:

SELECT COUNT(*), COUNT(CASE WHEN USER = 22 THEN 1 ELSE NULL END) AS has_voted
FROM favorites
WHERE content = 26977