reuse of subqueries with different WHERE clause

I have a query which I looks schematically like this

WITH sub_base AS (
SELECT type,
CASE WHEN a < 10 THEN 'GOOD'
     WHEN a BETWEEN 10 AND 100 THEN 'OK'
     ELSE 'BAD'
END AS a_q,
...
CASE WHEN z < 5 THEN 'GOOD'
     WHEN z BETWEEN 5 AND 50 THEN 'OK'
     ELSE 'BAD'
END AS z_q
FROM tbl
),

SELECT sub_one AS (
SELECT 'one' AS type,
COUNT(CASE WHEN a_q = 'GOOD' THEN 1) a_good,
COUNT(CASE WHEN a_q = 'OK' THEN 1) a_ok,
COUNT(CASE WHEN a_q = 'BAD' THEN 1) a_bad,
...
COUNT(CASE WHEN z_q = 'GOOD' THEN 1) z_good,
COUNT(CASE WHEN z_q = 'OK' THEN 1) z_ok,
COUNT(CASE WHEN z_q = 'BAD' THEN 1) z_bad,
FROM sub_base
WHERE type = 'one'
),

SELECT sub_two AS (
SELECT 'two' AS type,
COUNT(CASE WHEN a_q = 'GOOD' THEN 1) a_good,
COUNT(CASE WHEN a_q = 'OK' THEN 1) a_ok,
COUNT(CASE WHEN a_q = 'BAD' THEN 1) a_bad,
...
COUNT(CASE WHEN z_q = 'GOOD' THEN 1) z_good,
COUNT(CASE WHEN z_q = 'OK' THEN 1) z_ok,
COUNT(CASE WHEN z_q = 'BAD' THEN 1) z_bad,
FROM sub_base
WHERE type = 'two'
),

SELECT sub_all AS (
SELECT 'all' AS type,
COUNT(CASE WHEN a_q = 'GOOD' THEN 1) a_good,
COUNT(CASE WHEN a_q = 'OK' THEN 1) a_ok,
COUNT(CASE WHEN a_q = 'BAD' THEN 1) a_bad,
...
COUNT(CASE WHEN z_q = 'GOOD' THEN 1) z_good,
COUNT(CASE WHEN z_q = 'OK' THEN 1) z_ok,
COUNT(CASE WHEN z_q = 'BAD' THEN 1) z_bad,
FROM sub_base
)

SELECT * FROM sub_one
UNION
SELECT * FROM sub_two
UNION
SELECT * FROM sub_all

I want to reuse the subqueries in sub_one, sub_two, sub_all because they are almost identical with the exception of type in SELECT and the WHERE condition.

How can I avoid copy-pasting these subqueries?

Answer

Use GROUP BY:

SELECT type,
       COUNT(CASE WHEN a_q = 'GOOD' THEN 1) as a_good,
       COUNT(CASE WHEN a_q = 'OK' THEN 1) as a_ok,
       COUNT(CASE WHEN a_q = 'BAD' THEN 1) as a_bad,
       ...
       COUNT(CASE WHEN z_q = 'GOOD' THEN 1) as z_good,
       COUNT(CASE WHEN z_q = 'OK' THEN 1) as z_ok,
       COUNT(CASE WHEN z_q = 'BAD' THEN 1) as z_bad,
FROM sub_base
WHERE type IN ('one', 'two', 'three')  -- may not be needed
GROUP BY type;

In Postgres, I would further simplify this. You could use FILTER or summing binary values:

SELECT type,
       SUM( (a_q = 'GOOD')::int ) as a_good,
       SUM( (a_q = 'OK')::int ) as a_ok,
       SUM( (a_q = 'BAD')::int ) as a_bad,
       ...
       SUM( (z_q = 'GOOD')::int ) as z_good,
       SUM( (z_q = 'OK')::int ) as z_ok,
       SUM( (z_q = 'BAD')::int ) as z_bad,
FROM sub_base
WHERE type IN ('one', 'two', 'three')  -- may not be needed
GROUP BY type;

I would also dispense with the CTE and just put the logic in the outer query directly, but that is more a matter of style.

EDIT:

You can use another CTE:

WITH . . . ,
     t as (
      SELECT type,
             SUM( (a_q = 'GOOD')::int ) as a_good,
             SUM( (a_q = 'OK')::int ) as a_ok,
             SUM( (a_q = 'BAD')::int ) as a_bad,
             ...
             SUM( (z_q = 'GOOD')::int ) as z_good,
             SUM( (z_q = 'OK')::int ) as z_ok,
             SUM( (z_q = 'BAD')::int ) as z_bad,
      FROM sub_base
      WHERE type IN ('one', 'two', 'three')  -- may not be needed
      GROUP BY type
     )
SELECT t.*
FROM t
UNION ALL
SELECT 'Total', SUM(a_good), SUM(a_ok), SUM(a_bad), . . .
FROM t;