Mysql query: count column

Question

Hi all guys i have a technical problem, i have a table with records like this:

DATE        SIP DURATION  TYPE
2017-02-22  670    9      NO ANSWER
2017-02-22  670    9      ANSWER
2017-02-22  670    9      ANSWER
2017-02-22  671    9      NO ANSWER

i'd like have result like this:

SIP   DURATION  NOANSWER  ANSWER
670     18         1        2
671     9          1        0

i'll make query like this:

select 
DISTINCT(SIP),
(select count(*) FROM table where TYPE="ANSWERED")
(select count(*) FROM table where TYPE="NO ANSWERED")
from table

but the result count all record on the table, is better use group by?

sorry i'm noob :( thanks for the time Marco

Sebastian Brosch · Accepted Answer · 2017-02-22 17:09:30Z

1

In this case it is easier to use GROUP BY with conditional SUM and COUNT like the following example:

SELECT 
    SIP,
    SUM(CASE TYPE WHEN 'ANSWER' THEN DURATION ELSE 0 END) AS DURATION,
    COUNT(CASE WHEN TYPE = 'NO ANSWER' THEN 1 ELSE NULL END) AS NOANSWER,
    COUNT(CASE WHEN TYPE = 'ANSWER' THEN 1 ELSE NULL END) AS ANSWER
FROM table_name
GROUP BY SIP

Demo: http://sqlfiddle.com/#!9/b1142/2/0

edited Feb 22, 2017 at 17:09

answered Feb 22, 2017 at 16:58

Sebastian Brosch

43.8k15 gold badges78 silver badges89 bronze badges

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

Collectives™ on Stack Overflow

Mysql query: count column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related