CODEWITHSUNDEEP
mysql
Marco
Marco

Reputation: 35

Mysql query: count column

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

Upvotes: 1

Views: 26

Answers (1)

Sebastian Brosch
Sebastian Brosch

Reputation: 43564

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

Upvotes: 1

Related Questions