Efficient PostgreSQL query syntax for duplicating the result

Question

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
);

• Akina: Maybe it's more reasonable to filter and aggregate the table in CTE then build needed sums in outer query?
• jvkloc: @Akina please see the edited question. It doesn't seem to be a better way in any sense. Not worse, either.

Answer 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

• jvkloc: 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.
• Lennart - Slava Ukraini: @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.
• Erwin Brandstetter: 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 ...