Distinct + Sum 问题

[nauhil]

center　　cardno tickets
1　　 00000001 720
1　　 00000002 720
1　　 00000003 720
1　　 00000001 720
1　　 00000002 720
1　　 00000003 720

我现在统计唯一卡号的，tickets合计， count(distinct cardno) = 3, sum(tickets) = 2160 这个结果就对了

不能用group by 因为已经设定group by center了，以中心为单位

查询出来的结果应该是

center count tickets
1 3 2160

[中国风]

sum(tickets) 

改為

sum(distinct tickets) 

[叶子]

declare @T table (center int,cardno varchar(8),tickets int)
insert into @T
select 1,'00000001',720 union all
select 1,'00000002',720 union all
select 1,'00000003',720 union all
select 1,'00000001',720 union all
select 1,'00000002',720 union all
select 1,'00000003',720

select center,count(1) as [count] ,sum(tickets) as tickets
from (select distinct * from @T)aa
group by center
/*
center      count       tickets
----------- ----------- -----------
1           3           2160
*/

[nauhil]

[Quote=引用 1 楼 roy_88 的回复:]

SQL code
sum(tickets)

改為

sum(distinct tickets)
[/Quote]

不行，这样 sum 出来的结果 是720

[xuam]

 select center, count(1),sum(tickets)  from (select distinct  center,　　cardno, tickets   from T) t1

[-晴天]

select count(*),sum(tickets) from select distinct * from tb)t

[geniuswjt]

你临时表转换下不就可以group by了

--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (center int,cardno varchar(8),tickets int)
insert into [tb]
select 1,'00000001',720 union all
select 1,'00000002',720 union all
select 1,'00000003',720 union all
select 1,'00000001',720 union all
select 1,'00000002',720 union all
select 1,'00000003',720

--开始查询
select center,COUNT(*) [count],SUM(tickets) tickets from(
select center,cardno,tickets from tb
group by center,cardno,tickets) t
group by center

--结束查询
drop table [tb]

/*
center      count       tickets
----------- ----------- -----------
1           3           2160

(1 行受影响)

[中国风]

[Quote=引用 3 楼 nauhil 的回复:]

引用 1 楼 roy_88 的回复:

SQL code
sum(tickets)

改為

sum(distinct tickets)


不行，这样 sum 出来的结果 是720
[/Quote]
用2樓方法

[中国风]

或group by 

SELECT center,COUNT(cardno) AS cardno,SUM(tickets) AS tickets
FROM 
(SELECT  center,cardno,tickets FROM @T GROUP BY center,cardno,tickets)t 
GROUP BY center

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-10-11 09:37:00
-- Verstion:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
--	Apr 22 2011 11:57:00 
--	Copyright (c) Microsoft Corporation
--	Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([center] int,[cardno] varchar(8),[tickets] int)
insert [tb]
select 1,'00000001',720 union all
select 1,'00000002',720 union all
select 1,'00000003',720 union all
select 1,'00000001',720 union all
select 1,'00000002',720 union all
select 1,'00000003',720
--------------开始查询--------------------------
select center ,count(*),sum(tickets) from (select distinct * from tb)t group by center 
----------------结果----------------------------
/* center                  
----------- ----------- -----------
1           3           2160

(1 行受影响)
*/