April 18, 2012
Mysql addition and add them as new column
Question by Mohan Sinfh
I want to fetch 2 coulmns count and do their total as a new column.
How can I do this?
i wrote this query, but this is returning wrong total.
SELECT count(case when `status`='1' then 1 else 0 end) AS HOT,
count(case when `status`='5' then 1 end)
AS Special_Case,count(case when 1=1 then 1 end) AS TOTAL
FROM `tbl_customer_conversation` group by
date(`dt_added`),user_id
Answer by Starx
COUNT will only give the times a record is matched, which in your query will always return 1. Because the values can either be 1 or 0. So count(1) is also 1 and count(0) is also 1.
AS, you want the total number of HOT cases and SPECIAL_CASE you have to use SUM.
SELECT
SUM(case when `status`='1' then 1 else 0 end) AS HOT,
SUM(case when `status`='5' then 1 end) AS Special_Case,
SUM(case when `status` = '1' or `status` = '5' then 1 end) AS TOTAL
FROM `tbl_customer_conversation`
group by date(`dt_added`),user_id
