SQL里group by的问题

[fenghan_66]

代码如下:

select a.DocNum,a.CardName,Sum(b.OpenQty*b.Price) as '金额',
case when b.Currency!='RMB' then sum(b.GTotal/b.Quantity*b.OpenQty/b.rate) else sum(b.GTotal/b.Quantity*b.OpenQty) end as '净值',
Sum(b.OpenQty*b.Price)-sum(b.GTotal/b.Quantity*b.OpenQty) as '税收',a.NumatCard,a.DocDuedate,a.DocDate,a.TaxDate
from oqut as a left outer join QUT1 as b on a.DocEntry = b.DocEntry 
group by a.DocNum,a.CardName,a.NumatCard,a.DocDuedate,a.DocDate,a.TaxDate
/*
代码报错,一直让我把b.Currency这个字段放入Group by里,但是我需要它来做判断,不知道有什么办法..
各位高手赐教啊...
*/

[dobear_0922]

--try
select a.DocNum,a.CardName,Sum(b.OpenQty*b.Price) as '金额'
	,sum(case when b.Currency<>'RMB' then b.GTotal/b.Quantity*b.OpenQty/b.rate else b.GTotal/b.Quantity*b.OpenQty end) as '净值'
	,Sum(b.OpenQty*b.Price)-sum(b.GTotal/b.Quantity*b.OpenQty) as '税收'
	,a.NumatCard,a.DocDuedate,a.DocDate,a.TaxDate
from oqut as a left outer join QUT1 as b on a.DocEntry = b.DocEntry 
group by a.DocNum,a.CardName,a.NumatCard,a.DocDuedate,a.DocDate,a.TaxDate

[pt1314917]

select a.DocNum,a.CardName,Sum(b.OpenQty*b.Price) as '金额',
sum(case when b.Currency!='RMB' then b.GTotal/b.Quantity*b.OpenQty/b.rate else b.GTotal/b.Quantity*b.OpenQty end) as '净值',
Sum(b.OpenQty*b.Price)-sum(b.GTotal/b.Quantity*b.OpenQty) as '税收',
a.NumatCard,a.DocDuedate,a.DocDate,a.TaxDate
from oqut as a left outer join QUT1 as b on a.DocEntry = b.DocEntry 
group by a.DocNum,a.CardName,a.NumatCard,a.DocDuedate,a.DocDate,a.TaxDate

[想飞的狼]

sum汇总

[-狙击手-]

select a.DocNum,a.CardName,Sum(b.OpenQty*b.Price) as '金额',
sum(b.GTotal/b.Quantity*b.OpenQty/(case when b.Currency!='RMB' then b.rate else 1 end )) as '净值',
Sum(b.OpenQty*b.Price)-sum(b.GTotal/b.Quantity*b.OpenQty) as '税收',a.NumatCard,a.DocDuedate,a.DocDate,a.TaxDate
from oqut as a left outer join QUT1 as b on a.DocEntry = b.DocEntry 
group by a.DocNum,a.CardName,a.NumatCard,a.DocDuedate,a.DocDate,a.TaxDate

[liangCK]

select a.DocNum,a.CardName,Sum(b.OpenQty*b.Price) as '金额',
sum(b.GTotal/b.Quantity*b.OpenQty/(case when b.Currency!='RMB' then b.rate else 1 end )) as '净值',
Sum(b.OpenQty*b.Price)-sum(b.GTotal/b.Quantity*b.OpenQty) as '税收',a.NumatCard,a.DocDuedate,a.DocDate,a.TaxDate
from oqut as a left outer join QUT1 as b on a.DocEntry = b.DocEntry
group by a.DocNum,a.CardName,a.NumatCard,a.DocDuedate,a.DocDate,a.TaxDate