CODEWITHSUNDEEP
sqlsql-server
psj01
psj01

Reputation: 3245

Combining the results from two separate queries in to two columns

I am working with two sql queries that I would like to combine as one so the result of the first query would be in the first column and the result of the second one in column two. How can I acheive this?

I tried union, but it will put the results in two rows.. thats not what I want... 

select count(*) as ColumnA from Inventory i, Sale s where i.vin=s.vin and i.condition='new' 

select count(*) as ColumnB from Inventory i, Sale s where i.vin=s.vin and i.condition='used' order by 1 desc;

Upvotes: 2

Views: 13118

Answers (4)

Cᴏʀʏ
Cᴏʀʏ

Reputation: 107526

You can obtain both counts at the same time with a little bit different query, which will be slightly more efficient than combining two queries:

SELECT
    SUM(CASE WHEN i.condition = 'new' THEN 1 ELSE 0 END),
    SUM(CASE WHEN i.condition = 'used' THEN 1 ELSE 0 END)
FROM
    Inventory i
JOIN
    Sale s ON i.vin = s.vin

Upvotes: 8

Mikhail Shilkov
Mikhail Shilkov

Reputation: 35134

You can use combine two sub-queries in one query like this:

select
(select count(*) from Inventory i, Sale s where i.vin=s.vin and i.condition='new') as New,
(select count(*) from Inventory i, Sale s where i.vin=s.vin and i.condition='used') as Used

What were you trying to achieve with order by statement?

Upvotes: 10

Kritner
Kritner

Reputation: 13765

A simple way would be to:

select
    (select count(*) as ColumnA from Inventory i, Sale s where i.vin=s.vin and i.condition='new') as newCount,
    (select count(*) as ColumnB from Inventory i, Sale s where i.vin=s.vin and  i.condition='used') as usedCount

Upvotes: 3

dotnetom
dotnetom

Reputation: 24901

You can use another SELECT to combine results:

SELECT 
    (select count(*) from Inventory i, Sale s where i.vin=s.vin and i.condition='new') as ColumnA,
    (select count(*) from Inventory i, Sale s where i.vin=s.vin and i.condition='used') as ColumnB

Upvotes: 2

Related Questions