SQl get counts from multiple CTEs into one table

user_13151926 0
Viewed 311

I have multiple CTEs which all come from the same table, "table1". And all they return is a count of the rows. The differences between each CTE is the contents of the where statements as I want to compare the counts when I make these small changes. The code looks like:

WITH cte1 AS(
SELECT COUNT(*)
FROM table1
WHERE....
), cte2 AS(
SELECT COUNT(*)
FROM table1
WHERE....
), cte3 AS(
SELECT COUNT(*)
FROM table 1
WHERE....
)

I want to get an output where I get all the counts from each CTE and just have them in one table like:

cte1      cte2      cte3
1,564     3,567     2,861
amazon-redshift common-table-expression sql
2 Answers

As long as all of your CTEs return only 1 row you can just do the cartesian product of all of them (all in the from clause with no joins). I believe you will need to give a column alias to your count(*) expression to include them in your CTE as well.

WITH cte1 AS(
 SELECT COUNT(*) cnt 
 FROM table1
 WHERE....
), cte2 AS(
 SELECT COUNT(*) cnt
 FROM table1
 WHERE....
), cte3 AS(
 SELECT COUNT(*) cnt
 FROM table 1
 WHERE....
)
SELECT cte1.cnt AS cte1, cte2.cnt AS cte2, cte3.cnt AS cte3
FROM cte1, cte2, cte3

Also if you don't have any joins in your CTE you can potentially run the counts simultaneously by using sum case expressions which may increase performance.

SELECT SUM(CASE WHEN /* where clause 1 */ THEN 1 ELSE 0 END) cte1,
       SUM(CASE WHEN /* where clause 2 */ THEN 1 ELSE 0 END) cte2,  
       SUM(CASE WHEN /* where clause 3 */ THEN 1 ELSE 0 END) cte3
FROM table1
Ted_Elliott_165010 
SELECT
  (SELECT COUNT(*) FROM table1 WHERE....) as cte1,
  (SELECT COUNT(*) FROM table1 WHERE....) as cte2,
  (SELECT COUNT(*) FROM table1 WHERE....) as cte3
user_134735250
Related