0

Assuming I have the following PostgreSQL query:

SELECT (
        SELECT SUM (t.customers) 
        FILTER (
            WHERE id = 1
            AND date 
            BETWEEN %(start)s AND %(stop)s
        ) + 
        SELECT SUM (t.employees) 
        FILTER (
            WHERE id = 1
            AND date 
            BETWEEN %(start)s AND %(stop)s
        )
FROM table t;

and I want to duplicate the result so that the returned table has three columns. Should I copy the subquery three times, separated by a comma? That seems a messy way of doing it and not optimal as the query would be conducted three times(?). I'd rather I give an alias, let's say eaters, to the query and write a new query returning the alias three times:

-- The SELECT above AS eaters and then:
SELECT (
    SELECT * FROM eaters, SELECT * FROM eaters, SELECT * FROM eaters
);

That should be ok also performance wise, I guess. Is there some other neat effective way of duplicating the result n times inside the main query?

I query school restaurant data divided into three parts: total result, result without drinks and just the drinks. The number of eaters (= customers + employees) will be presented in each result table and is always the same for all three columns. Obviously there is the same number of customers, no matter which part is considered. So I need the same value three times. I could of course replace the following values with text, 'same as total' etc. and it would be understood. But I'd prefer learning about this and presenting the numbers.

EDIT: using CTEs I'd come up with this kind of query:

WITH customers AS (
    SELECT SUM (customers)
    FROM table
    WHERE id = 1
    AND date 
    BETWEEN %(start)s AND %(stop)s
), employees AS (
    SELECT SUM (employees)
    FROM table
    WHERE id = 1
    AND date 
    BETWEEN %(start)s AND %(stop)s
);
SELECT (
    (SELECT * FROM customers) + (SELECT * FROM employees)
) AS eaters;
SELECT (
    SELECT * FROM eaters, SELECT * FROM eaters, SELECT * FROM eaters
);
2
  • 1
    Maybe it's more reasonable to filter and aggregate the table in CTE then build needed sums in outer query?
    – Akina
    Dec 6, 2023 at 11:34
  • @Akina please see the edited question. It doesn't seem to be a better way in any sense. Not worse, either.
    – jvkloc
    Dec 6, 2023 at 11:41

1 Answer 1

1

I might not understand the problem, but:

SELECT my_sum, my_sum, my_sum
FROM (
    SELECT   SUM(t.customers) FILTER (
               WHERE id = 1
               AND date BETWEEN %(start)s AND %(stop)s
         ) + SUM(t.employees) FILTER (
               WHERE id = 1
               AND date BETWEEN %(start)s AND %(stop)s
         )  as my_sum
    FROM t
) AS tt;

If the filters are always the same, it may be better to move them to a where clause:

SELECT my_sum, my_sum, my_sum
FROM (
    SELECT SUM(t.customers) + SUM(t.employees) as my_sum
    FROM t
    WHERE id = 1
    AND date BETWEEN %(start)s AND %(stop)s
) AS tt;

Fiddle

3
  • This looks better, I didn't know that it's possible to "multiselect" like that. And thanks for the latter example, I'm new to SQL and didn't see that possibility.
    – jvkloc
    Dec 6, 2023 at 13:15
  • 1
    @jvkloc, SQL is closed in the sense that the result of a select query is a derived table. I.e. you can select from a query just fine. Dec 6, 2023 at 14:15
  • 1
    While Postgres allows duplicate identifiers in the outer query level, it's typically instrumental to assign unique column aliases. Like: SELECT my_sum AS eaters1, my_sum AS eaters2, my_sum AS eaters3 FROM ... Dec 6, 2023 at 19:58

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.