sum in 2 diffrent ways from the same table

hi there ,
this is my code :
ths code is working but i need to add more sum result
this sum res need to only for products the the Postive value is true
like that :

select ProRunId,ProMkt,ProName,ProMinInStock,ProRem,SupName,
 (select Sum (MoveDepQuant) - sum(MoveWithQuant)) as Bal

Here i need to add more Sum(MoveDepQuant) as Pos only for the products that have the
value where Postive=1


from dbo.Products
 inner join dbo.Moves on ProRunId=MoveProRunId inner join dbo.Supplier on Products.SupRunId=Supplier.SupRunId
 group by ProRunId,ProMkt,ProName,ProMinInStock,ProRem,SupName
order by ProMkt

thanks ....

select ProRunId,ProMkt,ProName,ProMinInStock,ProRem,SupName,
 (select Sum (MoveDepQuant) - sum(MoveWithQuant)) as Bal from dbo.Products 
 inner join dbo.Moves on ProRunId=MoveProRunId inner join dbo.Supplier on Products.SupRunId=Supplier.SupRunId
 group by ProRunId,ProMkt,ProName,ProMinInStock,ProRem,SupName
order by ProMkt

Open in new window

Tech_MenAsked:
Who is Participating?
 
Imran Javed ZiaConnect With a Mentor Consultant Software Engineer - .NET ArchitectCommented:
Hi,
You can try this

select ProRunId,ProMkt,ProName,ProMinInStock,ProRem,SupName,  
 (select Sum (MoveDepQuant) - sum(MoveWithQuant)) as Bal,
Sum(Case When Postive=1 Then MoveDepQuant Else 0 End) as Pos

from dbo.Products  
 inner join dbo.Moves on ProRunId=MoveProRunId inner join dbo.Supplier on Products.SupRunId=Supplier.SupRunId  
 group by ProRunId,ProMkt,ProName,ProMinInStock,ProRem,SupName  
order by ProMkt


Thanks
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you mean something along these lines:

select sum(field) total
  , sum( case when Postive = 1 then field else 0 end ) positive_total
  , sum( case when Postive = 0 then field else 0 end ) negative_total
  ...
0
 
Tech_MenAuthor Commented:
very good
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.