2

Considering a query with CTEs:

WITH cte_0 AS (
          SELECT SUM(col_0)
          FROM table_0
          WHERE condition_00
          AND condition_01
    ), 
    cte_1 AS (
          SELECT SUM(col_1)
          FROM table_0
          WHERE condition_10
          AND condition_11
    )
    SELECT (
          ((SELECT * FROM cte_0) * 100) / (SELECT * FROM cte_1),
          ((SELECT * FROM cte_0) + 100) * (SELECT * FORM cte_1)
    ) FROM cte_0, cte_1

Would it be correct to leave the last FROM out? And do I get a tuple of two numbers with a query like that? Or do I need a new table from which I SELECT * to get two numbers?

I just started my first PostgreSQL project and I've seen different styles of writing it. All comments on the style are warmly welcomed. I have not found any suitable online PostgreSQL platform for testing queries. Are there any?

Question edited to include conditions and a more complicated result.

0

1 Answer 1

3

Yes, it makes no sense to have that extra FROM, because you are not selecting anything from those tables, the results come entirely from the subqueries.

Either that, or you should remove the FROM in the subqueries, which would be more idiomatic.

In other words, you could also do this (note the use of an explicit CROSS JOIN rather than implicit comma-joins)

WITH cte_0 AS (
          SELECT SUM(col_0) sum0
          FROM table_0
          WHERE condition_00
          AND condition_01
    ), 
    cte_1 AS (
          SELECT SUM(col_1) sum1
          FROM table_0
          WHERE condition_10
          AND condition_11
    )
    SELECT
          sum0 * 100 / sum1,
          (sum0 + 100) * sum1
    FROM cte_0
    CROSS JOIN cte_1

Having said that, you shouldn't do any of these three options. You can do both aggregations from a single table reference.

In your (now modified) question, you can use conditional aggregation. In PostgreSQL this is done using the FILTER clause.

SELECT
  SUM(t0.col_0) FILTER (WHERE condition_00 AND condition_01) * 100
    / SUM(t0.col_1) FILTER (WHERE condition_10 AND condition_11),
  (SUM(t0.col_0) FILTER (WHERE condition_00 AND condition_01) + 100)
    * SUM(t0.col_1) FILTER (WHERE condition_10 AND condition_11)
FROM table0 t0;
2
  • 1
    I totally agree with the use of FILTER to get a single scan over table0, but I would still use a CTE or subquery to avoid the repetition
    – Bergi
    Dec 4, 2023 at 1:09
  • @Bergi could you add your solution as an answer so that I and all the future beginners finding the question could learn from it?
    – jvkloc
    Dec 5, 2023 at 6:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.