Calculate the Difference Between Results of Two Count(*) Queries Based on 2 Tables in Postgresql

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

Getting the difference of a count on 2 queries in mysql

Try with this:

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

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

PostgreSQL find the sum of two queries from different tables

select 
  (select count(*) from issuances_extended WHERE status = 'completed')
+ (select count(*) from redemptions_extended WHERE status = 'completed')
  AS result

Select count(*) from multiple tables

SELECT  (
        SELECT COUNT(*)
        FROM   tab1
        ) AS count1,
        (
        SELECT COUNT(*)
        FROM   tab2
        ) AS count2
FROM    dual

How do I add two count(*) results together on two different tables?

Wrap them up and use subqueries:

SELECT
(SELECT COUNT(*) FROM Toys WHERE little_kid_id = 900)+
(SELECT COUNT(*) from Games WHERE little_kid1 = 900 
                              OR little_kid2 = 900 
                              OR little_kid3 = 900)
AS SumCount

Voila!

Postgresql compare two select result on the same table

I suggest using SELECT in WITH (here documentation).

WITH orders_current_date AS (
    SELECT count(*)
    FROM order
    WHERE ordered_date > (NOW() - INTERVAL '120 minutes')  
          AND order_ordered = current_date)
), orders_interval AS (
    SELECT count(*)/3
    FROM order
    WHERE ordered_date > (NOW() - INTERVAL '2 days' - INTERVAL '120 minutes')  
          AND ordered_date < (NOW() - INTERVAL '2 days')
)
SELECT
       CASE
           WHEN SELECT * FROM orders_current_date > SELECT * FROM orders_interval
                THEN '1'
           ELSE 
                0
       END;

Combine two queries to count distinct strings with different filters

Much faster and simpler with conditional aggregates using the aggregate FILTER clause:

SELECT source
     , count(DISTINCT sku) FILTER (WHERE product_gap = 'yes') AS yes_gap
     , count(DISTINCT sku) FILTER (WHERE product_gap = 'no')  AS no_gap
FROM   product_gaps
WHERE  ingestion_date <= '2021-05-25'
GROUP  BY source;

See:

• Aggregate columns with additional (distinct) filters

Aside 1: DISTINCT is a key word, not a function. Don't add parentheses for the single column. distinct(sku) is short notation for DISTINCT ROW(sku). It happens to work because Postgres strips the ROW wrapper for a single column, but it's just noise.

Aside 2: product_gap should probably be boolean.

Select multiple count(*) in multiple tables with single query

A more traditional approach is to use "derived tables" (subqueries) so that the counts are performed before joins multiply the rows. Using left joins allows for all id's in basic to be returned by the query even if there are no related rows in either joined tables.

select
      basic.id
    , coalesce(a.LinkACount,0) LinkACount
    , coalesce(b.linkBCount,0) linkBCount
from basic 
left join (
     select id, Count(linkA_ID) LinkACount from LinkA group by id
     ) as a on a.id=basic.id
left join (
     select id, Count(linkB_ID) LinkBCount from LinkB group by id
     ) as b on b.id=basic.id

Sum of two counts from one table with additional data from another table

Something like this is typically fastest:

SELECT *
FROM   "TABLE_B" b
JOIN  (
   SELECT user_id AS id
        , count(*) FILTER (WHERE col_a)
        + count(*) FILTER (WHERE col_b) AS total
   FROM   "TABLE_A"
   GROUP  BY 1
   ) a USING (id);

While fetching all rows, aggregate first, join later. That's cheaper. See:

• Query with LEFT JOIN not returning rows for count of 0

The aggregate FILTER clause is typically fastest. See:

• For absolute performance, is SUM faster or COUNT?
• Aggregate columns with additional (distinct) filters

---

Often, you want to keep total counts of 0 in the result. You did say:

get the name of each user.

SELECT b.id AS user_id, b.name, COALESCE(a.total, 0) AS total
FROM   "TABLE_B" b
LEFT   JOIN (
   SELECT user_id AS id
        , count(col_a OR NULL)
        + count(col_b OR NULL) AS total
   FROM   "TABLE_A"
   GROUP  BY 1
   ) a USING (id);
...

count(col_a OR NULL) is an equivalent alternative, shortest, and still fast. (Use the FILTER clause from above for best performance.)

The LEFT JOIN keeps all rows from "TABLE_B" in the result.

COALESCE() return 0 instead of NULL for the total count.

---

If col_a and col_b have only few true values, this is typically (much) faster - basically what you had already:

SELECT b.*, COALESCE(aa.ct, 0) + COALESCE(ab.ct, 0) AS total
FROM   "TABLE_B" b
LEFT   JOIN (
   SELECT user_id AS id, count(*) AS ct
   FROM   "TABLE_A"
   WHERE  col_a
   GROUP  BY 1
   ) aa USING (id)
LEFT   JOIN (
   SELECT user_id AS id, count(*) AS ct
   FROM   "TABLE_A"
   WHERE  col_b
   GROUP  BY 1
   ) ab USING (id);

Especially with (small in this case!) partial indexes like:

CREATE INDEX a_true_idx on "TABLE_A" (user_id) WHERE col_a;
CREATE INDEX b_true_idx on "TABLE_A" (user_id) WHERE col_b;

Aside: use legal, lower-case unquoted names in Postgres to make your like simpler.

• Are PostgreSQL column names case-sensitive?

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.

---

Related Topics