CODEWITHSUNDEEP
sqlsql-server
Rosebud
Rosebud

Reputation: 589

How to combine two query's results into one row?

I have two queries that return one result each i.e one number

Select Count(*) as StockCountA from Table_A where dept='AAA'

Results

StockCountA 
550

.

Select Count(*) as StockCountB from Table_B where dept='BBB'

Results

StockCountB 
450

I wish to join the two results into one row record i.e

| StockCountA | StockCountB    
| 550         | 450

Upvotes: 31

Views: 57979

Answers (4)

GolezTrol
GolezTrol

Reputation: 116170

You can use:

select
(Select Count(*) as StockCountA from Table_A where dept='AAA') as StockCountA,
(Select Count(*) as StockCountB from Table_B where dept='BBB') as StockCountB

Explanation: you can select single value as a field in a select statement, so you could write something like

select
  x.*,
  (select Value from Table_Y y) as ValueFromY
from
  Table_X x

This will work only with scalar queries, meaning that the sub-query should have exactly 1 column, and at most 1 row. With 0 rows ValueFromY will return NULL and with more than 1 row, the query will fail.

An additional feature of select (in SQL Server, MySQL and probably others) is that you can select just values without specifying a table at all, like this:

Select
  3.14 as MoreOrLessPI

You can combine both those facts to combine the two counts into a single result, by writing a query that looks like:

Select
  (Select query that returns at most 1 row) as Result1,
  (Select another query that returns at most 1 row) as Result2

Upvotes: 59

Leptonator
Leptonator

Reputation: 3519

While not always the best practice, it is possible to do a CROSS JOIN..

SELECT
COUNT(Table_A.SOME_COLUMN) as StockCountA
,COUNT(Table_B.SOME_COLUMN) as StockCountB
FROM Table_A, Table_B WHERE Table_A.dept='AAA' AND Table_B.dept='BBB'

Upvotes: 0

Darshan Mehta
Darshan Mehta

Reputation: 30849

This should give you the desired result:

SELECT * FROM(
(Select Count(*) as StockCountA from Table_A where dept='AAA') StockCountA ,
(Select Count(*) as StockCountB from Table_B where dept='BBB') StockCountB
);

Upvotes: 8

Hemant Patel
Hemant Patel

Reputation: 184

Try below SQL : 

select (Select Count(*) as StockCountA from Table_A where dept='AAA')  StockCountA, 
       (Select Count(*) as StockCountB from Table_B where dept='BBB')  StockCountB

Hope This Helps :)

Upvotes: -1

Related Questions