'SELECT inside a COUNT

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
sql


Solution 1:[1]

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

Solution 2:[2]

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

Solution 3:[3]

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

Solution 4:[4]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Justin K
Solution 2 Ike Walker
Solution 3 Femaref
Solution 4 Jeroen

Related Questions