SQL 将重复行计数为单个

Question

我有 table

ID     State        District        StationCode        Status
---------------------------------------------------------------
 1    Gujarat       Banaskantha      12345               0 
 2    Gujarat       Banaskantha      12345               0
 3    M.P.          Bhopal           22315               1
 4    Gujarat       Banaskantha      12349               0
 5    Gujarat       Banaskantha      12345               1

我需要这样的结果

State      District       Active       InActive
-----------------------------------------------
Gujarat     Banaskantha      2            1
M.P.        Bhopal           0            1

在这里， Active和 Inactive字段是 Status 的总和字段基于 0或 1
这意味着这里 State古吉拉特邦，那里 three次 0发生了，但是 two StationCode - 12345 的重复行.这意味着它将被视为 One .

我有如下查询

select distinct 
    state,
    District,
    SUM(
        Case 
        when Status=0 then 1 
        else 0 end
        ) AS Active,
    SUM(
        Case 
            when Status=1 then 1 
            else 0 
        end
        ) AS InActive 
from 
    Station_Master 
group by state, District

但我无法计算重复 StationCode行为 Single。

我怎样才能做到这一点 ？

Answer 1

您可以唯一地过滤子查询中的行。尝试，

SELECT  DISTINCT state, district,
        SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END) Active,
        SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) InActive
FROM (
        SELECT DISTINCT state,  district, StationCode, status 
        FROM  Station_Master
    ) a
GROUP BY state, district

SQLFiddle Demo