I need 2 count columns in the same query in ORACLE-CodePudding

I'm trying to get the unique number of invoices a company has received and sent out using 2 count() functions. In invoices table there are two columns that are references to the same company id (one is id of a company that is sending an invoice and the other one is id of a company that is receiving an invoice) This is the code I tried using:

SELECT K.ID,K.NAME,K.CITY, COUNT(*) AS NUM_OF_INVOICES_SENT, COUNT(*) AS NUM_OF_INVOICES_RECEIVED
FROM COMPANY K LEFT JOIN INVOICE F ON F.COMP_SNEDING = K.ID 
GROUP BY K.NAME,K.ID,K.CITY

Result:

This is for a school project so I am in no means well versed in sql/oracle

SAMPLE DATA FOR INVOICE TABLE: SAMPLE DATA FOR COMPANY TABLE: actual data invoices: actual data company: desired outcome with given actual data:

CodePudding user response：

Here's one option; it doesn't use count, but sum with case expression.

Sample data:

SQL> with
  2  invoice (id, amount, comp_sending, comp_receiving) as
  3    (select 1, 2000 , 1, 2 from dual union all
  4     select 2, 28250, 3, 2 from dual union all
  5     select 3, 8700 , 4, 1 from dual union all
  6     select 4, 20200, 5, 3 from dual union all
  7     select 5, 21500, 3, 4 from dual
  8    ),
  9  company (id, name, city, state) as
 10    (select 1, 'Microsoft', 'Redmond'  , 'Washington' from dual union all
 11     select 2, 'Ubisoft'  , 'Paris'    , 'France'     from dual union all
 12     select 4, 'Starbucks', 'Seattle'  , 'Washington' from dual union all
 13     select 5, 'Apple'    , 'Cupertino', 'California' from dual union all
 14     select 3, 'Nvidia'   , 'Cupertino', 'California' from dual
 15    )

Query begins here:

 16  select c.id, c.name,
 17    sum(case when c.id = i.comp_sending   then 1 else 0 end) cnt_sent,
 18    sum(case when c.id = i.comp_receiving then 1 else 0 end) cnt_received
 19  from company c left join invoice i on c.id in (i.comp_sending, i.comp_receiving)
 20  group by c.id, c.name
 21  order by c.id;

        ID NAME        CNT_SENT CNT_RECEIVED
---------- --------- ---------- ------------
         1 Microsoft          1            1
         2 Ubisoft            0            2
         3 Nvidia             2            1
         4 Starbucks          1            1
         5 Apple              1            0

SQL>