SELECT inside a COUNT

Question

I would like to embed a SELECT inside a COUNT, but I can't find any examples.

#pseudosql
SELECT a AS current_a, COUNT(*) AS b,
   COUNT( SELECT FROM t WHERE a = current_a AND c = 'const' ) as d,
   from t group by a order by b desc

Does this answer your question? [How to get multiple counts with one SQL query?](https://stackoverflow.com/questions/12789396/how-to-get-multiple-counts-with-one-sql-query) — Braiam, Apr 17 '22 at 10:27

score 45 · Accepted Answer · answered Jul 16 '10 at 16:35

45

You don't really need a sub-select:

SELECT a, COUNT(*) AS b,
   SUM( CASE WHEN c = 'const' THEN 1 ELSE 0 END ) as d,
   from t group by a order by b desc

answered Jul 16 '10 at 16:35

Justin K

2,564
1
17
16

4

+1 This addresses the OP's specific query in a more efficient way. I would use `IF()` instead of `CASE` since there are only 2 states, but removing the sub-query is the right thing to do. – Ike Walker Jul 16 '10 at 17:23

score 28 · Answer 2 · answered Jul 16 '10 at 16:29

28

You can move the count() inside your sub-select:

SELECT a AS current_a, COUNT(*) AS b,
   ( SELECT COUNT(*) FROM t WHERE a = current_a AND c = 'const' ) as d,
   from t group by a order by b desc

answered Jul 16 '10 at 16:29

Ike Walker

62,183
14
104
106

So, Select inside a count is a syntax error in sql? – amk Oct 19 '16 at 15:37

score 1 · Answer 3 · answered Jul 16 '10 at 16:29

1

Use SELECT COUNT(*) FROM t WHERE a = current_a AND c = 'const' ) as d.

answered Jul 16 '10 at 16:29

Femaref

59,667
7
131
173

score -1 · Answer 4 · answered Jul 16 '10 at 16:29

-1

SELECT a AS current_a, COUNT(*) AS b,
   (SELECT COUNT(*) FROM t WHERE a = current_a AND c = 'const' ) as d
   from t group by a order by b desc

answered Jul 16 '10 at 16:29

Jeroen

4,023
2
22
40

SELECT inside a COUNT

4 Answers4