CODEWITHSUNDEEP
sqlpostgresql
Ucef
Ucef

Reputation: 565

Calculate the difference between results of two count(*) queries based on 2 tables in PostgreSQL

I want to calculate the difference between the results of 2 count(*)-type SELECT queries executed on 2 separate tables of my PostgreSQL database.

This what I'm currently using (however I should be able to wrap it all up into a single SELECT statement):

SELECT "count"(*) AS val1 FROM tab1;
SELECT "count"(*) AS val2 FROM tab2;
SELECT val2-val1;

Thanks in advance

Upvotes: 44

Views: 82149

Answers (5)

Harsh Hingorani
Harsh Hingorani

Reputation: 119

You can write many query to answer this question like: As I am considering the question as we have table named as STATION and we have to now finding the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table

Query 1:

select (count(city)-count(distinct city)) from station;

Query 2:

select ((select count(city) as ans1 from station)-(select count(distinct city)
       as ans2 from station));

Query 3:

select (( select count(city) from station )-( select count(distinct city) from station ))
       as ans;

All the above mentioned query will work.

Upvotes: 6

Aditya Tomar
Aditya Tomar

Reputation: 890

for same table you can do this.

select (count(abc)-count(DISTINCT xyz)) from tab;

Upvotes: 17

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

Another option is to use SUM:

SELECT SUM(val) AS result
FROM (SELECT count(*) AS val FROM tab2
      UNION ALL
      SELECT -count(*) FROM tab1) sub;

db<>fiddle demo

Upvotes: 0

Robert
Robert

Reputation: 25753

Try this way:

select 
  (
    SELECT 
      "count"(*) as val1 
    from 
      tab1
  ) - (
    SELECT 
      "count"(*) as val2 
    from 
      tab2
  ) as total_count

Upvotes: 59

user330315
user330315

Reputation: 

select t1.cnt - t2.cnt
from (select count(*) as cnt from tab1) as t1
  cross join (select count(*) as cnt from tab2) as t2

Upvotes: 10

Related Questions