SQL将重复行计为单个

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

这里,活动和非活动字段是基于0或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.

这意味着古吉拉特邦的状态,有三次出现0,但是两个重复的行用于StationCode - 12345.这意味着它将被视为一个。

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.

但是我无法将重复的StationCode行计为Single。

How can I do that ?

我怎样才能做到这一点 ?

5 个解决方案

#1

I think this would probably address your problem. I am not 100% sure of the syntax but if you can try it out and let me know if it doesnt work, i can try tweak it for you. The idea is to create a derived table using Group By to eliminate the duplicates that you dont want first and then the outer query is same as your original query - just on the derived table instead.

我想这可能会解决你的问题。我不是100%确定语法,但如果你可以尝试一下,让我知道如果它不起作用,我可以尝试为你调整它。我们的想法是使用Group By创建派生表以消除您首先不需要的重复项,然后外部查询与原始查询相同 - 仅在派生表上。

SELECT Distinct 
  X.State, 
  X.District, 
  SUM(Case when X.Status=0 then 1 else 0 end) AS Active,
  SUM(Case when X.Status=1 then 1 else 0 end) AS InActive
FROM 
  (Select State, District, StationCode, Status
   From Station_Master 
   Group By State,District, StationCode, Status) as X
GROUP BY X.State, X.District

#2

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

#3

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

在从中获取数据之前,您应该在子查询中使用DISTINCT子句或GROUP BY子句:

Using DISTINCT clause:

使用DISTINCT子句:

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:

使用GROUP BY子句:

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.

我还在表中添加了几条记录来检查这个SQLFiddle中的查询。工作得很好。

#4

Please use the Below SQL:

请使用下面的SQL:

declare @temptable table 
(
Id int,
state nvarchar(250),
District nvarchar(250),
StationCode nvarchar(250),
Status bit
)
Insert into @temptable values  (1,'Gujarat','Banaskantha','12345',0 )
Insert into @temptable values  (2,'Gujarat','Banaskantha','12345',0 )
Insert into @temptable values  (3,'M.P.','Bhopal','22315',1 )
Insert into @temptable values  (4,'Gujarat','Banaskantha','12349',0 )
Insert into @temptable values  (5,'Gujarat','Banaskantha','12345',1 )

select  tbl.state,tbl.District,SUM(cast(tbl.Status as int)) as Active ,(Count(*) -   
    SUM(cast(tbl.Status as int))) as InActive  
from (select distinct state,District,StationCode,Status from @temptable)as tbl 
group by tbl.state,tbl.District

#5

First retrieve the distinct tuples of (State, District, Status) and then run your aggregation query on top of it, like this:

首先检索(State,District,Status)的不同元组,然后在其上运行聚合查询,如下所示:

SELECT 
    a.state,
    a.District,
    SUM(
        Case 
        when a.Status=0 then 1 
        else 0 end
        ) AS Active,
    SUM(
        Case 
            when a.Status=1 then 1 
            else 0 
        end
        ) AS InActive 
from 
    (
        SELECT DISTINCT 
            state, 
            district, 
            status 
        FROM 
            Station_Master 
    ) as a
group by a.state, a.District