求一查询语句..关于统计的问题??

[kang123033]

A表:
UserName ItemID UnitPrice Quantity TotalPrice Date
client W123 20 1 20 2007-12-5
client W123 30 2 60 2007-12-5
client w18 25 1 25 2007-12-5
client w19 50 1 50 2007-12-6
Bobo w12 10 5 50 2007-12-6
Bobo W16 20 2 40 2007-12-6

得出结果如下:
UserName TotalItem TotalQuantity TotalPrice date
client 2 4 105 2007-12-5
client 1 1 50 2007-12-6
Bobo 2 7 90 2007-12-6

具体意思:根据字段"UserName"使用者及字段"Date"日期....统计出ItemID共多少个,Quantity的总数量,TotalPrice的总金额....谢谢

[wzy_love_sly]

格式很重要

[dobear_0922]

select UserName
	, TotalItem=count(ItemID)
	, TotalQuantity=sum(UnitPrice)
	, TotalPrice=sum(TotalPrice)
	, date
from A
group by UserName, date

[tim_spac]

select UserName
    , TotalItem=count(distinct ItemID)
    , TotalQuantity=sum(Quantity)
    , TotalPrice=sum(TotalPrice)
    , date
from A
group by UserName, date

[dobear_0922]

楼上正解，是应该加个distinct

[pt1314917]

create table tt(username varchar(10),itemid varchar(10),unitprict int,quantity int, totalprict int,date datetime)
insert into tt select 'client','W123',20,1,20,'2007-12-5'
insert into tt select 'client','W123',30,2,60,'2007-12-5'
insert into tt select 'client','w18',25,1,25,'2007-12-5'
insert into tt select 'client','w19',50,1,50,'2007-12-6'
insert into tt select 'Bobo','w12',10,5,50,'2007-12-6'
insert into tt select 'Bobo','W16',20,2,40,'2007-12-6'

select username,count(distinct itemid)TotalItem,
sum(quantity)TotalQuantity,sum(totalprict)TotalPrice,
date from tt group by username,date

[icelovey]

-- =============================================
-- Author:      T.O.P
-- Create date: 20091127
-- Version:     SQL SERVER 2000
-- =============================================
declare @TB table([UserName] varchar(6),[ItemID] varchar(4),[UnitPrice] int,[Quantity] int,[TotalPrice] int,[Date] datetime)
insert @TB
select 'client','W123',20,1,20,'2007-12-5' union all
select 'client','W123',30,2,60,'2007-12-5' union all
select 'client','w18',25,1,25,'2007-12-5' union all
select 'client','w19',50,1,50,'2007-12-6' union all
select 'Bobo','w12',10,5,50,'2007-12-6' union all
select 'Bobo','W16',20,2,40,'2007-12-6'

select UserName,  count(distinct ItemID) as TotalItem, sum(Quantity) as TotalQuantity, sum(TotalPrice) as TotalPrice,Date
from @TB
group by UserName,  Date
order by username desc


--测试结果:
/*
UserName TotalItem   TotalQuantity TotalPrice  Date                                                   
-------- ----------- ------------- ----------- ------------------------------------------------------ 
client   2           4             105         2007-12-05 00:00:00.000
client   1           1             50          2007-12-06 00:00:00.000
Bobo     2           7             90          2007-12-06 00:00:00.000

（所影响的行数为 3 行）
*/

[dawugui]

select UserName , count(distinct ItemID) TotalItem , sum(Quantity) TotalQuantity , sum(UnitPrice * Quantity) TotalPrice , Date from a group by UserName ,  Date 

[liangCK]

搞啥呀?..在挖坟?

[wtpgood]

select UserName
, count(ItemID) as TotalItem
, sum(UnitPrice) as TotalQuantity
, sum(TotalPrice) as TotalPrice
, date
from A
group by UserName, date

[快乐_石头]

[Quote=引用 8 楼 liangck 的回复:]
搞啥呀?..在挖坟?
[/Quote]
楼主发表于：2007-12-14 17:58:46