SQL count values from same column

Hi I have mysql table named content where i have a column "status" which have 3 values, converted, negotiating and 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?

thanks again

-------------Problems Reply------------

Use 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;

Category:php Views:2 Time:2018-08-07
Tags: php mysql

Related post

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.245 (s). 11 q(s)