How to combine two query's results into one row?
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
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
);