Sustract two queries from same table

Question

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.

Answer 1

SELECT b.name, a.*, a.sum2 - a.sum3 AS diff
FROM  (
    SELECT id
         , sum(CASE WHEN value_type = 2 THEN value::int END) AS sum2
         , sum(CASE WHEN value_type = 3 THEN value::int END) AS sum3
    FROM   data
    WHERE  id = 1
    AND    value_type IN (2, 3)
    AND    dayhour >= '2015-12-12 0:0'::timestamp
    AND    dayhour <  '2015-12-13 0:0'::timestamp
    GROUP  BY 1
    ) a
JOIN   device b USING (id);

• Assuming dayhour is of data type timestamp (information is missing). The cast to date would disable basic indexes. That's why I transformed it to a sargable predicate. More details:
  • Optimize BETWEEN date statement
• Why the cast value::int?
• Aggregate first, then join to device. Cheaper.