Comparing Two Count Results Using SQL

SQL: Comparing two counts from different Tables

Start off by getting the sums for each individually:

# Sum of lakes
SELECT code, count(*) AS sum FROM lakes GROUP BY code

# Sum of mountains
SELECT code, count(*) AS sum FROM mountains GROUP BY code

Then join the results together and select all rows where the sum of the mountains for a country are less than the number of lakes:

SELECT l.code AS code, l.sum AS lake_count, m.sum AS mountain_count
FROM
(SELECT code, count(*) AS sum FROM lakes GROUP BY code) AS l JOIN
(SELECT code, count(*) AS sum FROM mountains GROUP BY code) AS m
ON l.code = m.code
WHERE m.sum < l.sum

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

Try this way:

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

Can I compare two COUNT statements in the HAVING clause in SQL?

You are close, but because you are effectively doing an INNER JOIN between Copy and Loan, then COUNT(C.ISBN) will always be the same as COUNT(L.ISBN).

One way to fix this is to make it a LEFT JOIN

SELECT C.ISBN
FROM COPY C
    LEFT OUTER JOIN LOAN L ON C.ISBN = L.ISBN
GROUP BY C.ISBN
HAVING COUNT(C.ISBN) > COUNT(L.ISBN);

This should work as COUNT(column_name) will not include NULLs in the count.

While the above will work, however, it will actually produce a fairly large internal table during the calculation as (the JOIN condition is only on ISBN, meaning it will multiply out results) e.g., if there are 20 rows in Copy for a given ISBN, and 15 rows in Loans, COUNT(C.ISBN) will be 300 and COUNT(L.ISBN) will be 225 if my maths is correct.

There are ways to make it more efficient.

Alternate method 1: Include CopyNumber in the join e.g.,

SELECT C.ISBN
FROM COPY C
    LEFT OUTER JOIN LOAN L ON C.ISBN = L.ISBN AND C.CopyNumber = L.Copy
GROUP BY C.ISBN
HAVING COUNT(C.ISBN) > COUNT(L.ISBN);

Alternate method 2: Find specific CopyNumbers which aren't loaned out

You could also do this with a similar query but joined on ISBN and CopyNumber to find any specific copy numbers are not loaned out e.g.,

SELECT DISTINCT C.ISBN
FROM COPY C
    LEFT OUTER JOIN LOAN L ON C.ISBN = L.ISBN AND C.CopyNumber = L.Copy
WHERE L.ISBN IS NULL

Alternate method 3: Do the counts first, then join

SELECT C.ISBN
FROM (SELECT ISBN, COUNT(*) AS NumCopies FROM COPY GROUP BY ISBN) C
    LEFT OUTER JOIN 
    (SELECT ISBN, COUNT(*) AS NumLoans FROM LOAN GROUP BY ISBN) L 
       ON C.ISBN = L.ISBN AND C.CopyNumber = L.Copy
WHERE (C.ISBN > L.ISBN) OR (L.ISBN IS NULL);

These will all achieve the results you're trying to achieve with the initial query. But there is another question - is this the correct approach? What happens when a person returns a book? For a given book that is frequently lent out, would the data look like, say,

• 3 copies in the Copy table,
• but 50 rows in the Loans table

the next question is how would this approach deal with this properly?

Using SQL to compare counts of identifiers from two tables

To get the count for each key,

select count(*) as count, ID from Table1 group by ID

So, use this as a sub-query in the from clause, and join the tables.

select tt1.ID
from (select count(*) as count, ID from Table1 group by ID) tt1
  inner join (select count(*) as count, ID from Table2 group by ID) tt2
     on tt1.ID = tt2.ID
where tt1.count < tt2.count

Compare row count of two tables in a single query and return boolean

you have to remove :

FROM Table1,Table2

Otherwise it will consider the result of the Case-When for each row of this FROM clause.

Compare the results of a ROW COUNT

--this table will contain the count of occurences of each ID in tableA
declare @TableA_Results table(
ID bigint,
Total bigint
)
insert into @TableA_Results 
select ID,count(*) from database1.TableA
group by ID
--this table will contain the count of occurences of each ID in tableB
declare @TableB_Results table(
ID bigint,
Total bigint
);
insert into @TableB_Results 
select ID,count(*) from database2.TableB
group by ID
--this table will contain the IDs that doesn't have the same amount in  both tables 

declare @Discordances table(
ID bigint,
TotalA bigint,
TotalB bigint
)
insert into @Discordances
select TA.ID,TA.Total,TB.Total
from @TableA_Results TA
inner join @TableB_Results TB on TA.ID=TB.ID and TA.Total!=TB.Total
--the final output
select * from @Discordances

Compare two SQL tables and return count of rows with changes

Use full join (Full join returns joined records, plus not joined from left table, plus not joined from right table). Use case expressions with count():

select
       count(case when t1.num_key is null then 1 else null end) as cnt_new,
       count(case when t1.active_indicator = false and t2.active_indicator = true then 1 else null end) as cnt_false_to_true,
       count(case when t1.active_indicator = true and  t2.active_indicator = true then 1 else null end) as cnt_true_not_changed
 from (select * from table t1 where t1.ptn_dt = '2019-01-31') t1
      full join (select * from table t2 where ptn_dt = '2019-02-28' ) t2
           on t1.num_key = t2.num_key   

---

Related Topics