2 Selects running at once on the same data

[edmana]

I have a SQL query that uses derived tables (thanks George!). I now need to pull in summaries based on diff criteria at the same time. So, in mega query, I have

...
Select ID, Sum(Cost) from tblParts where Type ='L'
...
Select ID, Sum(Cost) from tblParts where Type<>'L'

(I am simplifying the code above).

It runs, but both columns return 0s. If I cut one out, it runs correctly. I assume this is because I am trying to run 2 selects on the same data in the same query at the same time.

Any ideas on how to fix?

Thanks!
Ed

 

[markros]

select ID, sum(case when Type = 'L' then Cost end) as TotalCost, sum(case when Type <> 'L' then Cost end) as OtherTotal from tblParts

PluralSight Learning Library

 

[r937]

don't forget the GROUP BY clause ;-)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markros]

Good point.

PluralSight Learning Library