CODEWITHSUNDEEP
mysql
gadam76
gadam76

Reputation: 17

COUNT in CASE statement?

I would like to categorize records based on the count of one field. My code is :

 select `posts`.`post_customer_fs_id` AS `post_customer_fs_id`,count(`post_id`) AS `open_post_count`,
CASE count(`post_id`) 
WHEN count(`post_id`)=1 THEN 'A'
WHEN count(`post_id`)>1 THEN 'B'
END AS AAA
from `posts` where (`posts`.`post_status` = '3') AND posts.post_type_id='1' group by `posts`.`post_customer_fs_id` ;

For some reason I m receiving A for the records with COUNT 1 but NULL for the records with COUNT bigger than 1. The post_id is an INTEGER field.. Any help it will be very appreciated!

Upvotes: 0

Views: 73

Answers (2)

Neo
Neo

Reputation: 135

TRY :::

select post_customer_fs_id AS 'post_customer_fs_id', count(post_id) AS 'open_post_count',
CASE 
    WHEN count(post_id)=1 THEN 'A'
    WHEN count(post_id)>1 THEN 'B'
    ELSE 'C'
END AS 'AAA'
from posts where post_status = '3' AND post_type_id= '1' 
group by post_customer_fs_id ;

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

In

CASE count(post_id) 
  WHEN count(post_id)=1 THEN 'A'

You are comparing count(post_id) with count(post_id)=1 where the latter results in true or false which equals 1 or 0 in MySQL. You want

CASE 
  WHEN count(post_id)=1 THEN 'A'

instead.

Upvotes: 1

Related Questions