CODEWITHSUNDEEP
phpmysqlsum
Matarishvan
Matarishvan

Reputation: 2422

calculate the count and then sum of total count MySql

My Query

$sql = 'SELECT status FROM tablename';

And the result

------------
status
------------
assigned
assigned
assigned
assigned
assigned
accepted
accepted
completed
completed
completed
completed
completed

Now i can find the total count of each status

SELECT status, COUNT(status) AS cnt
FROM tname
GROUP BY b.statusName
HAVING (cnt >= 1)

This will give

status     cnt
--------------
accepted    2
assigned    5
completed   5

How do i sum only completed and accepted count?

Upvotes: 0

Views: 231

Answers (3)

Mathi Vanan
Mathi Vanan

Reputation: 101

SELECT COUNT(status) AS cnt FROM tname WHERE status='completed' or status='accepted'

Upvotes: 0

Shadow
Shadow

Reputation: 34232

This is called conditional summing, when you place the condition within the sum() function:

SELECT SUM(IF(status IN ('accepted','assigned'),cnt,0)) as sum_of_acc_asg
FROM    
    (SELECT status, COUNT(status) AS cnt
    FROM tname
    GROUP BY b.statusName
    HAVING (cnt > 1)) t

Or you can use where to filter the subquery first:

SELECT SUM(cnt) as sum_of_acc_asg
FROM    
    (SELECT status, COUNT(status) AS cnt
    FROM tname
    WHERE status IN ('accepted','assigned')
    GROUP BY b.statusName
    HAVING (cnt > 1)) t

Upvotes: 2

narasimharaosp
narasimharaosp

Reputation: 533

Its simple, replace group by by where

SELECT status, COUNT(status) AS cnt
 FROM tname 
 WHERE b.statusName IN ('completed','accepted')
 HAVING (cnt > 1)

Upvotes: 1

Related Questions