数据汇总并按汇总信息筛选
有如下表结构：
customername productCode productName saleAmount saleDate
欣欣超市　　　　　1 软中华　　　　100　　　2007-1-1
华联商贸　　　　　2 硬中华　　　　50 2007-1-3
欣欣超市　　　　　1 软中华　　　　200 2007-1-6
文峰超市　　　　　1 软中华　　　　60 2007-1-7
欣欣超市　　　　　3 苏烟　　　　　50 2007-1-8
　　农工商超市　　　　2 硬中华　　　　300 2007-1-9
统计要求：可以按照查询要求统计　　在某段时间内，某一种或某几种商品达到一定销量的客户名单信息。比如：
在2007-1-1到2007-1-10间　软中华销量达到100-400区间的客户是:欣欣超市
在2007-1-1到2007-1-10间　软中华销量达到60-100区间的客户是:文峰超市
基本的SQL语句我写成如下：
select sum(saleamount) as allamount,customername,productname from View_ProductSellInfoDetail where allamount>=100 and allamount<=400 group by customername,productname
但执行时出错。说是：“列名'allamount'无效”
请教高手该如何操作



[解决办法]
select sum(saleamount) as allamount,customername,productname from View_ProductSellInfoDetail where allamount >=100 and allamount <=400 group by customername,productname


--------------

select sum(saleamount) as allamount,customername,productname
from View_ProductSellInfoDetail
group by customername,productname
having sum(saleamount) between 100 and 400
[解决办法]
--还要加上日期


select sum(saleamount) as allamount,customername,productname
from View_ProductSellInfoDetail
where saleDate between '2007-01-10' and '2007-01-10'
group by customername,productname
having sum(saleamount) between 100 and 400
[解决办法]
use this:

SQL code

select * from(select sum(saleAmount) as allamount, customerName, productName from View_ProductSellInfoDetailgroup by customername, productname)twhere allamount >= 100 and allamount <= 400
[解决办法]
sorry, i am wrong. using having is the right way.

SQL code
select sum(saleAmount) as allamount, customerName, productNamefrom View_ProductSellInfoDetailgroup by customername, productnamehaving sum(saleAmount) between 100 and 400
[解决办法]
SQL code
declare @t table(customername nvarchar(5), productCode int, productName nvarchar(5), saleAmount int,saleDate datetime)insert @t select '欣欣超市',1,          '软中华',100, '2007-1-1' insert @t select '华联商贸',2,          '硬中华',50 ,     '2007-1-3' insert @t select '欣欣超市',1,          '软中华',200,     '2007-1-6' insert @t select '文峰超市',1,          '软中华',60 ,     '2007-1-7' insert @t select '欣欣超市',3,          '苏烟',50 ,     '2007-1-8' insert @t select '农工商超市',2,         '硬中华', 300,     '2007-1-9' select     customername,    productName,    sum(saleAmount)as '销量' from     @t where     saleDate between '2007-1-1' and '2007-1-10'group by     customername,productName having    (sum(saleAmount) between 100 and 400 )