SQL count values from same column

Question

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

You can not expect formatting to be done in MySql to output to your page. It has to be done in the language you are using. — Vickrant
– Vickrant, Commented May 22, 2015 at 5:42

Sougata Bose · Accepted Answer · 2015-05-22 05:50:07Z

3

Use GROUP By. Try this -

SELECT status, count(status) result FROM content GROUP BY status

answered May 22, 2015 at 5:50

Sougata Bose

31.8k8 gold badges52 silver badges89 bronze badges

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

1 Comment

Samia Ruponti Over a year ago

Thanks man, i am so dumb that i was thinking the query in a very difficult way...

BhandariS · Accepted Answer · 2015-05-22 05:58:53Z

1

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(*).

edited May 22, 2015 at 5:58

answered May 22, 2015 at 5:53

BhandariS

6048 silver badges20 bronze badges

1 Comment

Samia Ruponti Over a year ago

just from curiosity what does that 1 mean in count?

Paul King · Accepted Answer · 2015-05-22 05:43:52Z

0

Will this work for you?

SELECT status, count(status) FROM content GROUP BY status;

answered May 22, 2015 at 5:43

Paul King

651 gold badge1 silver badge8 bronze badges

Collectives™ on Stack Overflow

SQL count values from same column

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related