Hi I have mysql table named content where i have a column "status" which have 3 values,
received. now i want to count how many have status received, negotiating, and converted for developing a chart.
here is what i used:
SELECT status, SUM(CASE WHEN status = 'converted' = 1 THEN 1 ELSE 0 END) AS converted, SUM(CASE WHEN status = 'negotiating' = 1 THEN 1 ELSE 0 END) AS negotiating, SUM(CASE WHEN status = 'Received NA' = 1 THEN 1 ELSE 0 END) AS ReceivedNA FROM content GROUP BY status;
It shows me the result but in a way that i can not use it.
to feed my chart i used this:
$data = array( array('converted', $converted), array('negotiating', $negotiating), array('received', $received) );
So i guess some thing like this table will solve my problem:
status result --------------------------- -------- converted 1 negotiating 5 received 4
So can anyone suggest how can modify my sql to get the expected result?
GROUP By. Try this -
SELECT status, count(status) result FROM content GROUP BY status
To get the distinct count use GROUP BY.
select status,count(1) as result from content GROUP BY status;
Instead of using sum, count is always a better and easier way
EDIT-to answer the comment
The parameter to the COUNT function is an expression that is to be evaluated for each row. The COUNT function returns the number of rows for which the expression evaluates to a non-null value. ( * is a special expression that is not evaluated, it simply returns the number of rows.)
There are two additional modifiers for the expression: ALL and DISTINCT. These determine whether duplicates are discarded. Since ALL is the default, your example is the same as count(ALL 1), which means that duplicates are retained.
Since the expression "1" evaluates to non-null for every row, and since you are not removing duplicates, COUNT(1) should always return the same number as COUNT(*).
Will this work for you?
SELECT status, count(status) FROM content GROUP BY status;