求一SQL语句

Question

有order,orderNum两表orderNum.id=order.OrderNumId
orderNum: id varchar(12),time varchar(50),state int
order:id int,BuyNum int,OrderNumId varchar(12),Pid int,THid int
求在开始时间到结束时间内查询[根据time],THid等于0,不重复的productsId，对应的BuyNum的和id          BuyNum      OrderNumId   productsId   TuiHuoId
----------- ----------- ------------ -----------  -----------
35          1           201011130001 44           0
36          1           201011130002 44           0
37          1           201011140001 45           0
38          1           201011150001 44           0
39          2           201011150002 89           0
40          3           201011170001 44           0
41          2           201011170001 88           0
42          1           201011210003 79           0结果 BuyNum        productsId   
 ----------- ------------ 
   6           44                   
   1           45                   
   2           89                 
   2           88           
   1           79           

Answer 1

select sum(a.BuyNum) BuyNum,
     b.productsId   
from orderNum a , [order] b
where a.id=b.OrderNumId
group by b.productsId   

Answer 2

select sum(a.BuyNum) BuyNum, b.productsId   
from orderNum a ,[order] b
where a.id = b.OrderNumId and b.THid = 0 and a.[time] between @starttime and @endtime
group by b.productsId   

Answer 3

我要扩展的话 会报错
该列没有包含在聚合函数或 GROUP BY 子句中。
比如我要例如把  b.productsId   转化为 p.id,p.name
inner join products p on p.id=b.productsId
用where 也报错

Answer 4

select sum(b.BuyNum) BuyNum,p.id,p.[name]
from orderNum a ,[order] b,Products p
where a.id = b.OrderNumId and p.id=b.productsId  and b.TuiHuoid = 0 
group by p.id
这样的话 把p.name加上去会报错 怎么办

Answer 5

恩 不错 看来我对group by不够了解 学习