Oracle sql combine table and do counting-CodePudding

How to count the total project participation (it means the project count) for the project Name Lakers and participate in Client meeting between 12/25-27/2022 for all employee display with the UserAc and their total PJCount？

Also, if the employee UserAc do not appear in the ProjectParticipate. It also need to display their UserAc and the PJCount is 0 in the final output when they are exists in the EMPLOYEE Table.

Table EMPLOYEE：

UserAc
Ken1
John1
Alex1
Dan1

Table ProjectParticipate

PJName    UserAc    PJRn    PDate        PJCount
Lakers    Ken1      0200    12/25/2022   5.3
Lakers    Ken1      0200    12/28/2022   8.7
Lakers    Ken1      0200    12/27/2022   4.3
Bulls     Ken1      0200    12/25/2022   4
Lakers    John1     0100    12/25/2022   6
Lakers    Alex1     0200    12/25/2022   3

Table ProjectGuide

PJName    PJNumber    NumberExplan
Lakers    0100        Phone call
Lakers    0200        Client meeting
Bulls     0100        Phone call
Bulls     0200        Team meeting
State     0100        Discussion
State     0200        Documentation

Here is the current query I develop

Select 
    UserAc,
    COUNT(PJCount) 
from
    ProjectParticipate
Where 
    PJRn = ‘0200’ and PDate BETWEEN 12/25/2022 and 12/27/2022
--it seems to need to use TABLE ProjectGuide to know Project:Lakers, PJNumber:0200 = Client meeting instead of just PJRn = 0200
Group by 
    UserAc

I don’t know how to use and combine 3 tables to fulfill the requirements.

Below is the expected results

UserAc      PJCount
Ken1        9.6
John1       0
Alex1       3
Dan1        0

Thank you so much for your help.

CodePudding user response：

You can use left join to get PJCount of only 'Client meeting' and PDate between 25th and 27th December. Instead of COUNT() you need to use conditional SUM().

Schema and insert statement:

 CREATE TABLE EMPLOYEE(UserAc VARCHAR(50));

 INSERT INTO EMPLOYEE VALUES('Ken1');
 INSERT INTO EMPLOYEE VALUES('John1');
 INSERT INTO EMPLOYEE VALUES('Alex1');
 INSERT INTO EMPLOYEE VALUES('Dan1');

 CREATE TABLE ProjectParticipate(PJName VARCHAR(50), UserAc VARCHAR(50), PJRn VARCHAR(50), PDate DATE, PJCount FLOAT);

 INSERT INTO ProjectParticipate VALUES('Lakers',    'Ken1',      '0200',   DATE'2022-12-25',   5.3);
 INSERT INTO ProjectParticipate VALUES('Lakers',    'Ken1',      '0200',    DATE'2022-12-28',   8.7);
 INSERT INTO ProjectParticipate VALUES('Lakers',    'Ken1',      '0200',    DATE'2022-12-27',   4.3);
 INSERT INTO ProjectParticipate VALUES('Bulls',     'Ken1',      '0200',    DATE'2022-12-25',   4);
 INSERT INTO ProjectParticipate VALUES('Lakers',    'John1',     '0100',    DATE'2022-12-25',   6);
 INSERT INTO ProjectParticipate VALUES('Lakers',    'Alex1',     '0200',    DATE'2022-12-25',   3);

 CREATE Table ProjectGuide(PJName VARCHAR(50),    PJNumber    VARCHAR(50), NumberExplan VARCHAR(50))

 INSERT INTO ProjectGuide VALUES('Lakers','0100','Phone call');
 INSERT INTO ProjectGuide VALUES('Lakers','0200','Client meeting');
 INSERT INTO ProjectGuide VALUES('Bulls','0100','Phone call');
 INSERT INTO ProjectGuide VALUES('Bulls','0200','Team meeting');
 INSERT INTO ProjectGuide VALUES('State','0100','Discussion');
 INSERT INTO ProjectGuide VALUES('State','0200','Documentation');

Query:

 Select 
     E.UserAc,
     SUM(case when PJNumber is not null then PP.PJCount else 0 end ) PJCount
 from
     EMPLOYEE E LEFT JOIN ProjectParticipate PP ON E.UserAc=PP.UserAc
     and PP.PDate BETWEEN DATE'2022-12-25' and DATE'2022-12-27'
     LEFT JOIN ProjectGuide PG ON PP.PJName=PG.PJName AND PP.PJRn=PG.PJNumber 
     and PG.NumberExplan = 'Client meeting'
 
 Group by 
     E.UserAc

Output

USERAC	PJCOUNT
Ken1	9.6
Alex1	3
John1	0
Dan1	0

db<>fiddle here

CodePudding user response：

use union. And not count but sum But you are missing the link between EMPLOYEE and ProjectGuide. You need to find or create that relationship Then wrap it all as inline view


SELECT UserAc, Count(cnt)
FROM 
( -- START INLINE VIEW

Select 
    UserAc,
    SUM(PJCount) cnt
from
    ProjectParticipate
Where 
    PJRn = '0200' and 
    (PDate BETWEEN to_date('12/25/2022', 'MM/DD/YYYY') and to_date('12/27/2022', 'MM/DD/YYYY'))
Group by 
    UserAc

UNION

Select 
    st.UserAc,
    0 cnt
from
    ProjectGuide pg inner join 
    some_table st on pg.PJNumber = st.PJNumber -- <-- you nee to find or define this relation
Group By 
    st.UserAc
) T  -- END INLINE VIEW
GROUP BY 
    UserAc

Union suppresses exact records. So, John/9 and John/0 will return 2 records and sum will be correct. This is why you group by again.

CodePudding user response：

Use an IN clause to find the PJ name/number that represent a client meeting. Use an outer join to include users that did not attend. (And use proper date literals. And you get a sum with SUM, not with COUNT.)

select e.userac, coalesce(pp.sum_pjcount, 0) as total
from employee e
left outer join
(
  select userac, sum(pjcount) as sum_pjcount
  from projectparticipate
  where pdate between date '2022-12-25' and date '2022-12-27'
  and (pjname, pjrn) in 
  (
    select pjname, pjnumber
    from projectguide
    where numberexplan = 'Client meeting'
  )
  group by userac
) pp on pp.userac = e.userac
order by e.userac;