SQL Multiple select count statements in one query

Question

I have data with no relation. Just need to count various columns from 3 tables and display them on the page as a view.

This is the code so far but doesn't work:

SELECT COUNT(cars) AS A,
       (SELECT COUNT(boats) FROM tableBoats) AS B,
       (SELECT COUNT(trees) FROM tableTrees) AS C,
 FROM tableCars

Solution 1

SELECT A, B, C
FROM (SELECT COUNT(cars) as A FROM tableCars) a
CROSS JOIN (SELECT COUNT(boats) as B FROM tableBoats) b
CROSS JOIN (SELECT COUNT(trees) as C FROM tableTrees) c

should do it.

Solution 2

Assuming you have a table like here (tableXxx tables having a field named xxx), your query had a syntax error by having a comma after AS C,, without that comma, it works properly (at least using sqlite, because mssql is not working at sqlfiddle for me):

http://sqlfiddle.com/#!5/5fa6c/3

SELECT COUNT(cars) AS A,
       (SELECT COUNT(boats) FROM tableBoats) AS B,
       (SELECT COUNT(trees) FROM tableTrees) AS C
FROM tableCars

BTW, you could simplify your query to

SELECT (SELECT COUNT(cars ) FROM tableCars ) AS A,
       (SELECT COUNT(boats) FROM tableBoats) AS B,
       (SELECT COUNT(trees) FROM tableTrees) AS C

The other answers are also perfect :)

Solution 3

How about this?

SELECT
    (SELECT COUNT(*) FROM tableCars) car_count,
    (SELECT COUNT(*) FROM tableBoats) boat_count,
    (SELECT COUNT(*) FROM tableTrees) tree_count

Solution 4

Following on Luc M's response, which provides a single list, vs columns with separate values. You can imagine how useful this might be...

SELECT C.Accountnum as AccountNum, C.Address as Address, 'C' as Source 
From CustTable C 
Where C.AccountNum like '000%' 
Union All 
Select V.Accountnum as AccountNum, V.Name as Address, 'V' as Source 
from VendTable V 
Where V.AccountNum like 'A%'

View more solutions