CODEWITHSUNDEEP
mysqlsqlcountsumwhere-clause
user2700022
user2700022

Reputation: 509

SQL: Sum of count statements containing where clause

I have 3 count statements, which I need to combine and take a sum of. 

SELECT COUNT(status) 
FROM tableA 
WHERE env='test' 
AND deploy_date='2016-10-19' 
AND  platform='MA' 
AND server='B' 
AND status='5';

SELECT COUNT(status) 
FROM tableA 
WHERE env='test' 
AND deploy_date='2016-10-19'  
AND  platform='MA'  
AND server='B' 
AND status='3';

SELECT COUNT(status) 
FROM tableA 
WHERE env='test' 
AND deploy_date='2016-10-19' 
AND  platform='MA' 
AND server='B' 
AND status='1';

I tried 

SELECT (select count(status) 
FROM tableA 
WHERE env='test' 
AND deploy_date='2016-10-19' 
AND  platform='MA' 
AND server='B' and status='5'; ) + 
(SELECT COUNT(status) 
FROM tableA WHERE env='test' 
AND deploy_date='2016-10-19' 
AND  platform='MA' 
AND server='B' AND status='3';) + 
(SELECT COUNT(status) 
FROM tableA
WHERE env='test' 
AND deploy_date='2016-10-19' 
AND  platform='MA' 
AND server='B' AND status='1';)

But getting syntax error. What am I missing? Is there a better way to accomplish this in MySQL. Thanks in advance

Upvotes: 0

Views: 87

Answers (3)

muffaddal shakir
muffaddal shakir

Reputation: 81

SELECT count(status)
FROM   tableA 
WHERE  env = 'test'
   AND deploy_date = '2016-10-19'
   AND platform = 'MA'
   AND server = 'B'
   AND status IN ( '5', '3', '1' );

You can combine the statement into one, status differ, rest all the filtration are same.

For status wise count, add group by clause Group By Status at the end of the SQL script.

SELECT Status,count(status)
FROM   tableA
WHERE  env = 'test'
   AND deploy_date = '2016-10-19'
   AND platform = 'MA'
   AND server = 'B'
   AND status IN ( '5', '3', '1' )
Group By Status

Upvotes: 1

Husen
Husen

Reputation: 1587

Please try this one, it is very simple query.

SELECT status, count(status) from tableA 
WHERE env='test' AND deploy_date='2016-10-19' AND platform='MA' AND server='B' AND status in ('1','3','5')
Group by status

above will return individual count and sum if you want sum of all then just remove group by clause.

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93754

Here is one way to combine

SELECT count(1)
FROM   tableA
WHERE  env = 'test'
       AND deploy_date = '2016-10-19'
       AND platform = 'MA'
       AND server = 'B'
       AND status IN ( '5', '3', '1' );

Upvotes: 1

Related Questions