Geek asks and answers

SQL Multiple Counting Instances in One Query

I have data without a relationship. You just need to read the various columns from 3 tables and display them on the page as a view.

This code is still, but not working:

SELECT COUNT(cars) AS A,
       (SELECT COUNT(boats) FROM tableBoats) AS B,
       (SELECT COUNT(trees) FROM tableTrees) AS C,
 FROM tableCars
+5
source share
5 answers
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

must do it.

+11
source

Assuming you have a table like this ( tableXxxtables having a field with a name xxx), your query had a syntax error, having a comma after AS C,, without this comma, it works correctly (at least using sqlite, because mssql is not working on 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

By the way, you can simplify your request to

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

Other answers are also perfect :)

+12

?

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

Following Luc M's answer, which provides a single list, vs columns with separate values. You can imagine how useful this is ...

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%'
+1
source 
SELECT *
FROM 
(
   SELECT 'Nb cars'  as description, COUNT(cars)  AS count_item FROM tableCars
   UNION ALL
   SELECT 'Nb boats' as description, COUNT(boats) AS count_item FROM tableBoats
   UNION ALL
   SELECT 'Nb tress' as description, COUNT(trees) AS count_item FROM tableTrees
) temp
0
source

More articles:


All Articles