CODEWITHSUNDEEP
sqloracle-database
mabanger
mabanger

Reputation: 147

The difference between two count statements

I have two count statements in SQL and I would like to get the difference between them. I was trying something like this.

SELECT  'Customers After', SUM(Counts) Counts
FROM
(SELECT 'Customers Before', COUNT(*) AS Counts FROM CUSTOM3
MINUS
SELECT  'Customers to be', COUNT(*) AS Counts FROM
CUSTOM3 a
WHERE 
    a.CUSTOMER_FLAG IS NULL
AND NOT EXISTS (SELECT 1 FROM ACC2 acc WHERE acc.CUSTOMER_ID = a.CUSTOMER_ID)
AND CUST_BQ IS NULL);

But then it simply returns the number of rows, what should I use instead of MINUS or how should I start it?

I should get something like this:

Customers After 15
Customers Before 20
Customers to be 5

Upvotes: 3

Views: 4128

Answers (3)

Kacper
Kacper

Reputation: 4818

I like code using with it seems more clear to me:

with cb as (SELECT COUNT(*) AS Counts FROM CUSTOM3),
     ctb as (SELECT COUNT(*) AS Counts FROM CUSTOM3 a
WHERE a.CUSTOMER_FLAG IS NULL
AND NOT EXISTS (SELECT 1 FROM ACC2 acc WHERE acc.CUSTOMER_ID = a.CUSTOMER_ID)
AND CUST_BQ IS NULL)
     select 'Customers Before' as description, counts from cb union all
     select 'Customers to be' as description, counts from ctb union all
     select 'Customers After' as description, cb.counts - ctb.counts as counts from cb, ctb

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191315

When you do this:

SELECT 'Customers Before', COUNT(*)
MINUS
SELECT  'Customers to be', COUNT(*)

... the minus is not doing anything because the first column value in the second query is not the same. Even if that string was the same, you'd either see the same data (if the counts are different) or no rows returned (if the counts are the same). I think you're just misunderstanding what the minus set operator does.

If you want the difference between the counts you need to subtract their values:

SELECT  'Customers After',
  (SELECT COUNT(*) FROM CUSTOM3)
  - (SELECT COUNT(*) FROM CUSTOM3 a
      WHERE a.CUSTOMER_FLAG IS NULL
      AND NOT EXISTS (SELECT 1 FROM ACC2 acc WHERE acc.CUSTOMER_ID = a.CUSTOMER_ID)
      AND CUST_BQ IS NULL) Counts
FROM dual;

or only count the rows that don't match the 'minus' criteris (verifying null handling):

SELECT  'Customers After', COUNT(*) AS Counts
FROM CUSTOM3 a
WHERE NOT (a.CUSTOMER_FLAG IS NULL
AND NOT EXISTS (SELECT 1 FROM ACC2 acc WHERE acc.CUSTOMER_ID = a.CUSTOMER_ID)
AND CUST_BQ IS NULL);

or

SELECT  'Customers After', COUNT(*) AS Counts
FROM CUSTOM3 a
WHERE a.CUSTOMER_FLAG IS NOT NULL
OR EXISTS (SELECT 1 FROM ACC2 acc WHERE acc.CUSTOMER_ID = a.CUSTOMER_ID)
OR CUST_BQ IS NOT NULL;

or if you actually want to see all three counts in the result set, use a subquery as @krokodilko showed.

Upvotes: 3

krokodilko
krokodilko

Reputation: 36107

Use a subquery and a case expression:

SELECT Customers_before, Customers_after,
       Customers_before - Customers_after As Customers_to_be
FROM (
    SELECT count(*) As Customers_before,
           count( case when a.CUSTOMER_FLAG IS NULL
                       and not exists( 
                            SELECT 1 FROM ACC2 acc 
                            WHERE acc.CUSTOMER_ID = a.CUSTOMER_ID)
                       and CUST_BQ IS NULL
                   then 1 end ) as Customers_after
    FROM CUSTOM3 a
)

Upvotes: 5

Related Questions