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

Question

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

GolezTrol · Accepted Answer · 2019-05-14T10:03:59.490

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

Thanks if you know the basics then you know what your doing otherwise your are an expert :) — Rosebud, Dec 01 '14 at 22:13
Yeah, that's why I decided to add a bit of explanation after all. It's hard to find the right documentation I you don't know what to look for. :) — GolezTrol, Dec 01 '14 at 22:14

score 7 · Answer 2 · answered Dec 01 '14 at 22:14

7

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
);

answered Dec 01 '14 at 22:14

Darshan Mehta

30,102
11
68
102

Interesting variation on the other answers. You are basically using the old (pre 1992) join syntax to cross join the two queries. The result will be the same, though. – GolezTrol Dec 01 '14 at 22:17

Leptonator · Answer 3 · 2014-12-02T14:21:28.663

0

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'

edited Dec 02 '14 at 14:21

answered Dec 01 '14 at 22:30

Leptonator

3,379
2
38
51

score -1 · Answer 4 · edited Dec 01 '14 at 22:07

-1

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 :)

edited Dec 01 '14 at 22:07

crthompson

15,653
6
58
80

answered Dec 01 '14 at 22:06

Hemant Patel

184
6

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

4 Answers4

Linked