Clever Geek Handbook

I have used group on demand to get the desired result

This is my table (Tbl_12042014) with fields like recordId, batchName, SubaBatchname, OpStatus1, OpStatus2.

   RecordId     Batchname   SubBatchname     OpStatus1    OpStatus2
       1         12042014      raw3         D            D
       2         12042014      raw3         D            D
       3         12042014      raw4         Null         Null
       4         12042014      raw4         Null         Null

I have used this query with a group, but I did not get the desired result.
I want the count recordId and column to have the status "D" for the corresponding subbatchName. fields with a zero value should be rejected.

Query

SELECT BatchName,SubBatchName,Count(RecordId)AS Records,
(Select count ( a.Opstatus1)  FROM  Tbl_12042014 as a where Opstatus1='D' ) as DE1,
(Select count(b.Opstatus2)  FROM  Tbl_12042014 as b where Opstatus2='D' )as DE2 
FROM  Tbl_12042014 GROUP BY BatchName, SubBatchName

I got the result for example by doing the above query. I got DE1, DE2 - 2 for raw3 and 2 for raw4.

Batchname   SubBatchname    Records DE1  DE2   
12042014    raw3               2     2    2   
12042014    raw4               2     2    2

But my desired output is like DE1, DE2 should be 2 for raw3 and 0 for raw4.

Batchname   SubBatchname    Records   DE1  DE2   
12042014    raw3              2        2    2     
12042014    raw4              2        0    0
+3


source to share


1 answer


you can do it with case based aggregation 



SELECT BatchName,SubBatchName, Count(RecordId)AS Records,
sum(case when Opstatus1='D' then 1 else 0 end) as DE1,
sum(case when Opstatus2='D' then 1 else 0 end) as DE2 
FROM  Tbl_12042014 GROUP BY BatchName, SubBatchName
+1


source






More articles:


All Articles