January 30, 2009 at 6:15 am
hello , i´ve this query
local mNdossier
select distinct BI.REF, BI.DESIGN , BI.QTT, ST.STOCK , Diferencial = ( sum(ST.STOCK)-BI.QTT) from bi (nolock) inner join bo (nolock) on bi.bostamp=bo.bostamp INNER JOIN ST ON BI.REF=ST.REF where BI.OBRANO = 100 and BI.PRODUCAO = 0 and bi.ndos=9 AND (ST.STOCK-BI.QTT)<0 group by bi.ref,bi.design,bi.qtt,st.stock order by bi.ref,bi.design,bi.qtt,st.stock
i need to group the results by ref
at this point the query returns this
ref design qtt stock diferencial
9009204 bicicleta 10 0 -10
9009201 bicicleta 2 5 0 -5
9009201 bicicleta 2 4 0 -4
and i need this
ref design qtt stock diferencial
9009204 bicicleta 10 0 -10
9009201 bicicleta 2 9 0 -9
help aprreciated.
January 30, 2009 at 6:21 am
You need to use the sum aggregate function in the select statement
select distinct
BI.REF,
BI.DESIGN,
SUM(BI.QTT),
SUM(ST.STOCK),
sum(ST.STOCK - BI.QTT ) AS Diferencial
from
bi (nolock) inner join bo (nolock) on bi.bostamp = bo.bostamp INNER JOIN ST ON BI.REF = ST.REF
where
BI.OBRANO = 100 and BI.PRODUCAO = 0 and bi.ndos = 9 AND ( ST.STOCK - BI.QTT ) < 0
group by
bi.ref,
bi.design,
order by
bi.ref,
bi.design,
January 30, 2009 at 12:22 pm
thank you very much for the support, the final result was this and is working !!
select distinct BI.REF,BI.DESIGN,SUM(BI.QTT) as QTT_A_PRODUZIR, ST.STOCK, dif=(st.stock-sum(bi.qtt))
from bi (nolock) inner join bo (nolock) on bi.bostamp = bo.bostamp INNER JOIN ST ON BI.REF = ST.REF
where BI.OBRANO = 100 and BI.PRODUCAO = 0 and bi.ndos = 9
group by bi.ref,bi.design,st.stock
having st.stock<sum(bi.qtt)
order by bi.ref, bi.design,st.stock
thank you
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply