SQL count duplicate row as single

Question

I have 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

I need result like

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

Here , Active and Inactive fields are sum of Status fields based on 0 or 1

That means here State for Gujarat, There three times 0 occured , but two duplicate rows for StationCode - 12345. It means it will be considered as One.

I have query like below

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

But I am unable to count duplicate StationCode row as Single.

How can I do that ?

Answer 1

you can uniquely filter the rows in a subquery. try,

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

Answer 2

You should use either DISTINCT clause or GROUP BY clause in sub-query before fetch data from it:

Using DISTINCT clause:

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;

Using GROUP BY clause:

SELECT 
    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
(
    SELECT state,District,StationCode,Status
    FROM Station_Master 
    GROUP BY state, District, Stationcode,Status
) A
GROUP BY state, District;

See this SQLFiddle

I also added few records in table to check the query in this SQLFiddle. And worked fine.