sql value based on a couple variables

quantity      product      desction          type          saleamount
315              xyz        product1                                      100
315             mnd        product2                                         200
315        dnd        product3                                          563
14             dioa        product4                                       456
315             xxx         product5              J                       144
1             ddd        product6                                        226
315             dqi         product7                                         144


So i'm trying to sum the saleamount product based on the following

take the quantity of the row where type is 'J' (So this would be 315) then SUM sales amount column if quantity has 315.  so the value would be 1151
this would be dynamic since this is just an example.
Southern_GentlemanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
can you explain more.. not clear when to take the SUM sales amount .
Éric MoreauSenior .Net ConsultantCommented:
Would it be:

select sum(saleamount)
from TableName
where quantity = (select top 1 Quantity from TableName where type = 'J')

Open in new window

Pawan KumarDatabase ExpertCommented:
Please try this ..tested and dynamic solution ..
This should work for larger data. Please let us know in case of any changes required.

SELECT r.Quantity,r.product,r.desction,r.type,CASE WHEN r.type = 'j' THEN g.saleamount1 ELSE r.saleamount END saleamount FROM 
(
	SELECT * , CASE WHEN [type] = 'J' THEN Quantity END st
	FROM SumQuantity
)r
CROSS APPLY
(
	SELECT SUM(saleamount) saleamount1
	FROM SumQuantity s 
	WHERE s.Quantity = r.st
)g

Open in new window


OUTPUT
/*------------------------
OUTPUT
------------------------*/
Quantity    product desction        type saleamount
----------- ------- --------------- ---- -----------
315         xyz     product1        NULL 100
315         mnd     product2        NULL 200
315         dnd     product3        NULL 563
14          dioa    product4        NULL 456
315         xxx     product5        J    1151
1           ddd     product6        NULL 226
315         dqi     product7        NULL 144

(7 row(s) affected)

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.