Let's say I have 2 tables:
table device
is a set of measuring devices, table data
is a set of values of different types measured by the devices.
Table data = (no, id,value_type,value, dayhour) no is PK, id refer to table device
Table device = (id, name) id is PK
I currently have a query that will obtain the sum of all values of a specific value_type
generated by an id
on a specific date, something like:
SELECT SUM(cast(a.value as int)),b.name FROM data a INNER JOIN device b
ON a.id=b.id
AND a.id=1
AND a.value_type=2
AND date(a.dayhour)='2015-12-12'
GROUP BY b.name
The query works without problems. Now I want to be able to subtract the sum of different value types. What I'm currently doing is two queries, one for obtaining the sum for value_type
2 and another for the sum for value_type
3 and then doing the subtraction at an upper layer process.
However, I would like to do this from a single query, something like a query that will retrieve one column with the sum of value_type
2, another with the sum of value_type
3 and a third one with the subtraction of these 2 columns.
SELECT b.name, a.*, a.sum2 - a.sum3 AS diff
FROM (
SELECT id
, sum(value::int) FILTER (WHERE value_type = 2) AS sum2
, sum(value::int) FILTER (WHERE value_type = 3) AS sum3
FROM data
WHERE id = 1
AND value_type IN (2, 3)
AND dayhour >= '2015-12-12'::timestamp
AND dayhour < '2015-12-13'::timestamp
GROUP BY 1
) a
JOIN device b USING (id);
Assuming dayhour
is of data type timestamp
.
The cast to date
would disable basic indexes. Use sargable predicates like demonstrated instead. See:
Use the aggregate FILTER
clause for conditional aggregates (since Postgres 9.4). See:
Why the cast value::int
?
Aggregate first, then join to device. Cheaper.