SUM with CASE Statement in mysql

Question

I have following Mysql query

SELECT c.`id`
    ,c.`category_name`
    ,c.`category_type`
    ,c.bookmark_count
    ,f.category_id cat_id
    ,f.unfollow_at
    ,(
        CASE WHEN c.id = f.follower_category_id 
               THEN (
                        SELECT count(`user_bookmarks`.`id`)
                        FROM `user_bookmarks`
                        WHERE (`user_bookmarks`.`category_id` = cat_id)
                            AND ((`f`.`unfollow_at` > `user_bookmarks`.`created_at`) || (`f`.`unfollow_at` = '0000-00-00 00:00:00'))
                        ) 
               ELSE 0 END
        ) counter
    ,c.id
    ,f.follower_category_id follow_id
    ,c.user_id
FROM categories c
LEFT JOIN following_follower_categories f ON f.follower_category_id = c.id
WHERE c.user_id = 26
ORDER BY `category_name` ASC

and here is output what i am getting after execuation See Screen

now i just want to count . here i have field id having value 172 against it i have counter 30,3, 2 and Bookmark_count is 4( i need to include only once)

and i am accepting output for id 172 is 30+3+2+4(bookmark_count only once).

I am not sure how to do this.

Can anybody help me out

Thanks a lot

Do you want to return 3 rows for id=172 (all having bookmark_count=4 and counter = 39), or only one row? — Yigitalp Ertem
– Yigitalp Ertem, Commented Sep 3, 2014 at 12:57

Yigitalp Ertem · Accepted Answer · 2014-09-03 13:05:33Z

1

The following may be the most inefficient query for that purpose, but I added a cover to your query in order to hint at grouping the results. (I removed the second c.id, and my example may have errors since I couldn't try it.)

SELECT `id`,
       `category_name`,
       `category_type`,
       max(`bookmark_count`),
       `cat_id`,
       `unfollow_at`,
       sum(`counter`)+max(`bookmark_count`) counter,
       follow_id`, `user_id`
FROM
(SELECT c.`id`
    ,c.`category_name`
    ,c.`category_type`
    ,c.bookmark_count
    ,f.category_id cat_id
    ,f.unfollow_at
    ,(
        CASE WHEN c.id = f.follower_category_id
               THEN (
                        SELECT count(`user_bookmarks`.`id`)
                        FROM `user_bookmarks`
                        WHERE (`user_bookmarks`.`category_id` = cat_id)
                            AND ((`f`.`unfollow_at` > `user_bookmarks`.`created_at`) || (`f`.`unfollow_at` = '0000-00-00 00:00:00'))
                        )
               ELSE 0 END
        ) counter
    ,f.follower_category_id follow_id
    ,c.user_id
FROM categories c
LEFT JOIN following_follower_categories f ON f.follower_category_id = c.id
WHERE c.user_id = 26)
GROUP BY `id`, `category_name`, `category_type`, `cat_id`, `unfollow_at`, `follow_id`, `user_id`
ORDER BY `category_name` ASC

answered Sep 3, 2014 at 13:05

Yigitalp Ertem

2,06926 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

soccer7 Over a year ago

there is no counter field exists

Collectives™ on Stack Overflow

SUM with CASE Statement in mysql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related