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