sum operation

Question

sum operation

sa.ordekci

Ten Centuries

Points: 1177
More actions
May 3, 2012 at 6:21 am

#257501

Hi.
i have a table ProductTrack.Table is like below:
ProductTrackId PK
ProductId int
SizeId int
Quantity int
Process int
i keep track product stocks with this table. i give some record example in this tablee
ProductTrackId ProductId SizeId Quantity Process
1 10 1 5 0
2 10 2 4 0
3 10 1 1 1
...
record are like this. Here, process field shows that process added,or saled. zero means that a new stock has added with related quantity. 1 means that a product has saled wtih related quantity. how can i take sum of this product according to sizeid. my query return like this.
Producttitle Size Sum
i take producttitle and Size name on other tables. My problem is take the sum.
Thanks in advance.

Viewing 4 posts - 1 through 3 (of 3 total)

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

CapnHector SSCoach Points: 16143 More actions · Answer 1

Im having some dificulty understanding what you want out. also readily consumable DDL and Sample data would help us out a lot. please see my signature for the link on the best practices for asking questions.

Help us help you.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

sa.ordekci Ten Centuries Points: 1177 More actions · Answer 2

i have a table ProductTrack.Table is like below: Table field are :

ProductTrackId primary key

ProductId int

SizeId int

Quantity int

Process int

i keep track product stocks with this table. ProductId field is related with my product table. In product table, i keep productname, code etc. sizeid field is related with size table. in size table , i keep size names. Process field takes two value; zero or one. if process field is zero, i understand that a new stock has added with related quantity(Quantity). if process field is one, i understand that a product has saled.

thing that i want is to find sum of the product for each size.

my table structure is like above.

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 3

Hope this helps

SELECTProductId, SizeId, SUM( Quantity * CASE WHEN Process = 1 THEN -1 ELSE 1 END ) Total

FROMProductTrack

GROUP BY ProductId, SizeId

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/