group by

Question

Post reply

group by

carlos cachulo-318532

Say Hey Kid

Points: 697
More actions
October 23, 2008 at 12:43 pm

#214441

Hi
i´ve tried this query with no sucess, i´m new to sql as you can see
select ref,design,qtt,armazem,* from sl group by armazem
the sql reports
Msg 8120, Level 16, State 1, Line 1
Column 'sl.ref' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
what i want to do is based on this records from table SL
ref design qtt armazem
------------------ ------------------------------------------------------------ --------------------------------------- ---------------------------------------
ARTIGO1 artigo 1 100.000 1
ARTIGO1 artigo 1 300.000 2
ARTIGO1 artigo 1 100.000 1
ARTIGO1 artigo 1 45.000 2
ARTIGO2 artigo 2 268.000 3
is something like this
ref design qtt armazem
ARTIGO1 artigo 1 200 1
ARTIGO1 artigo 1 345 2
ARTIGO2 artigo 2 268 3
thanks in advance,

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 1

Try this. If a coulumn is not in the group by, it must use an aggregate function like sum.

Select ref, design, sum(qtt) sumqtt, armazem

FROM SL

GROUP BY ref, design, armazem

carlos cachulo-318532 Say Hey Kid Points: 697 More actions · Answer 2

hi

thank you very much, it worked just fine

carlos a. cachulo

carlos cachulo-318532 Say Hey Kid Points: 697 More actions · Answer 3

hi again

one problem just came up,

in this quantities some are out orders and others are in orders,

example

if sl.origem='BO' +

and if sl.origem='FT' -

is is possible to reflect this on the SUM function??

thanks in advance

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Try something like this

SELECT ref, design, armazem, SUM(CASE origem WHEN 'BO' THEN qtt WHEN 'FT' THEN -1*qtt ELSE 0 END) AS sumqtt

FROM SL

GROUP BY ref, design, armazem

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

carlos cachulo-318532 Say Hey Kid Points: 697 More actions · Answer 5

hello

thanks, sorry for this dummie questions, i´m begining to discover sql

thank you veru much

carlos a. cachulo