CODEWITHSUNDEEP
sqloracle-database
Franco
Franco

Reputation: 25

How to do multiple queries from a single table at once and do math calculations?

I would like to get the following result but struggle to query the logic. What I want is to get the exact number of transactions/invoices after removing the canceled transactions (- negative custnb) from the table. SO with my sample table, I have 5 transactions, 2 are canceled, so I would like to only get the 3.

WANTED RESULT

Invoices     Customers

3            3

TABLE

invoicenumber         custnb     invoiceid

    1001              1          1001
    1002              2          1002
    1003              1          1003
    1004              5          1004
    1005              2          1005
    2000001           -1         1001
    2000002           -2         1002

Upvotes: 0

Views: 40

Answers (3)

Littlefoot
Littlefoot

Reputation: 142705

The way you put it, this returns desired result; lines #1 - 9 represent sample data, so code you need begins at line #10.

SQL> with test (invoicenumber, custnb, invoiceid) as
  2    (select 1001,  1, 1001 from dual union all
  3     select 1002,  2, 1002 from dual union all
  4     select 1003,  1, 1003 from dual union all
  5     select 1004,  5, 1004 from dual union all
  6     select 1005,  2, 1005 from dual union all
  7     select 2001, -1, 1001 from dual union all
  8     select 2002, -2, 1002 from dual
  9    )
 10  select count(invoicenumber) invoices,
 11         count(custnb) customers
 12  from test
 13  where custnb > 0
 14    and invoicenumber not in (select invoiceid
 15                              from test
 16                              where custnb < 0
 17                             )
 18  ;

  INVOICES  CUSTOMERS
---------- ----------
         3          3

SQL>

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269543

One method is to aggregate at the invoice level and then count. Uncancelled invoices will have a "positive" custnb:

select count(*) as num_invoices, count(distinct custnb) as num_customers
from (select invoiceid
      from t
      group by invoiceid
      having min(custnb) > 0
     ) t;

If the custnb and invoiceids line up exactly (as they now do), I would approach this using not exists:

select count(*) as num_invoices, count(distinct custnb) as num_custoers
from t
where not exists (select 1
                  from t t2
                  where t2.invoiceid = t.invoiceid and
                        t2.custnb = - t.custnb
                 );

Or possibly using except:

select count(*) as num_invoices, count(distinct custnb)
from ((select invoiceid, custnb
       from t
       where custnb > 0
      ) except
      (select invoiceid, - custnb
       from t
       where custnb < 0
      )
     ) ic

Upvotes: 0

Gro
Gro

Reputation: 1683

Untested but it would be something like this. Also, assumption is a customer may have multiple invoices.

select count(t.invoicenumber) invoices, count(distinct t.custnb) customers
from table t
where t.custnb > 0
and not exists (
 select 1 from table t2 
 where t2.invoiceid = t.invoiceid
 and t2.custnb < 0)

Upvotes: 0

Related Questions