CODEWITHSUNDEEP
sqlpostgresql
Wboy
Wboy

Reputation: 2542

Subtraction of counts of 2 tables

I have 2 different tables, A and B. A is something like created and b is removed

I want to obtain the nett difference of the counts per week in an SQL query.

Currently I have

SELECT DATE_TRUNC('week', TIMESTAMP AT time ZONE '+08') AS Week,
       Count(id) AS "A - New"
FROM table_name.A
GROUP BY 1
ORDER BY 1

This gets me the count per week for table A only. How could I incorporate the logic of subtracting the same Count(id) from B, for the same timeframe?

Thanks! :)

Upvotes: 0

Views: 2408

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35563

The potential issue here is that for any week you might only have additions or removals, so to align a count from the 2 tables - by week - an approach would be to use a full outer join, like this:

SELECT COALESECE(A.week, b.week) as week
     , count_a
     , count_b
     , COALESECE(count_a,0) - COALESECE(count_b,0) net
FROM (
    SELECT DATE_TRUNC('week', TIMESTAMP AT time ZONE '+08') AS week
         , Count(*) AS count_A
    FROM table_a
    GROUP BY DATE_TRUNC('week', TIMESTAMP AT time ZONE '+08')
    ) a
FUUL OUTER JOIN (
    SELECT DATE_TRUNC('week', TIMESTAMP AT time ZONE '+08') AS week
         , Count(*) AS count_b
    FROM table_b
    GROUP BY DATE_TRUNC('week', TIMESTAMP AT time ZONE '+08')
    ) b on a.week = b.week

Upvotes: 3

Jinesh Shah
Jinesh Shah

Reputation: 952

The usual syntex for substracting values from 2 queries is as follows

Select (Query1) - (Query2) from dual;

Assuming both the tables have same number of id in 'id' column and your given query works for tableA, following query will subtract the count(id) from both tables. 

select(SELECT DATE_TRUNC('week', TIMESTAMP AT time ZONE '+08') AS Week,
   Count(id) AS "A - New" FROM table_name.A GROUP BY 1 ORDER BY 1) - (SELECT DATE_TRUNC('week', TIMESTAMP AT time ZONE '+08') AS Week,
   Count(id) AS "B - New" FROM table_name.B GROUP BY 1 ORDER BY 1) from dual

Or you can also try the following approach

Select c1-c2 from(Query1 count()as c1),(Query2 count() as c2);

So your query will be like

Select c1-c2 from (SELECT DATE_TRUNC('week', TIMESTAMP AT time ZONE '+08') AS Week, Count(id) AS c1 FROM table_name.A GROUP BY 1 ORDER BY 1),(SELECT DATE_TRUNC('week', TIMESTAMP AT time ZONE '+08') AS Week, Count(id) AS c2 FROM table_name.B GROUP BY 1 ORDER BY 1);

Upvotes: 0

Related Questions