CODEWITHSUNDEEP
sqloracle-databasejoinaggregate-functions
AWS_Developer
AWS_Developer

Reputation: 856

SQL Group By + Aggregate functions with joins

I have two tables: 1. EMPLOYEE_DATA and its columns as

EMP_ID  MANAGER_ID
9999      2222
8888      2222
7777      2222

Another table is : 2. EMPLOYEE_RECORDS:

EMP_ID MANAGER_ID NO_OF_SWIPEINS ATTENDACE_DATE
9999   2222         1             23-DEC-2017
7777   2222         1             23-DEC-2017
9999   2222         1             23-DEC-2017
9999   2222         1             23-DEC-2017

I want the data in below format:

EMP_ID        MANAGER_ID       TODAY_SWIPEINS
9999            2222                 3
8888            2222                 0
7777            2222                 1

I tried below query:

    SELECT edata.EMP_ID, sum(rec.NO_OF_SWIPEINS) as TODAY_SWIPEINS,edata.MANAGER_ID FROM EMPLOYEE_RECORDS  rec 
right JOIN EMPLOYEE_DATA  edata
ON edata.MANAGER_ID = rec.MANAGER_ID 
and rec.MANAGER_ID='2222' and trunc(rec.ATTENDANCE_DATE)='23-DEC-2017'
group by edata.EMP_ID ,edata.MANAGER_ID;

But getting below results:

EMP_ID    TODAY_SWIPEINS      MANAGER_ID
8888           4                2222
7777           4                2222
9999           4                2222

Someone please guide me what I am doing wrong.

Upvotes: 1

Views: 72

Answers (6)

nagraj036
nagraj036

Reputation: 175

SELECT edata.EMP_ID, nvl(sum(rec.NO_OF_SWIPEINS), 0) as TODAY_SWIPEINS,edata.MGR_ID 
FROM EMPONE  edata left JOIN MGRONE  rec
ON edata.EMP_ID = rec.EMP_ID 
group by edata.EMP_ID ,edata.MGR_ID

Upvotes: 0

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

You were close, but why were you joining by manager_id?. NULL handling was required while calculating SUM. Also, it is preferable to use TO_DATE while doing comparison with a date column, attendance_date

SELECT edata.emp_id,
       edata.manager_id,
       SUM(CASE
             WHEN rec.no_of_swipeins IS NULL THEN 0
             ELSE rec.no_of_swipeins
           END) AS TODAY_SWIPEINS
FROM   employee_records rec
       RIGHT JOIN employee_data edata
               ON edata.emp_id = rec.emp_id
                  AND rec.manager_id = '2222'
                  AND TRUNC(rec.attendance_date) =
                      To_date('23-DEC-2017', 'DD-MON-YYYY'
                      )
GROUP  BY edata.emp_id,
          edata.manager_id;

DEMO

Upvotes: 0

cdaiga
cdaiga

Reputation: 4939

Here is the query that does what you wanted, the ATTENDANCE_DATE is set to the current date.

SELECT B.EMP_ID, B.MANAGER_ID, COUNT(A.NO_OF_SWIPEINS) TODAY_SWIPEINS FROM 
(SELECT * FROM EMPLOYEE_RECORDS WHERE ATTENDACE_DATE=DATE_FORMAT(NOW(),'%d-%b-%Y')) A
RIGHT JOIN EMPLOYEE_DATA B
ON A.EMP_ID=B.EMP_ID
GROUP BY B.EMP_ID, B.MANAGER_ID;

Here is an SQL Fiddle Demo of the above query.

Upvotes: 0

Badiparmagi
Badiparmagi

Reputation: 1285

you should select data from employee_data and then join with table 2.

check this:

SELECT ED.MANAGER_ID, ED.EMP_ID, COUNT(NO_OF_SWIPEINS), IFNULL(ATTENDACE_DATE, '23-DEC-2017') as ATTENDACE_DATE  FROM EMPLOYEE_DATA ED
LEFT JOIN EMPLOYEE_RECORDS ER
ON ER.EMP_ID = ED.EMP_ID AND ATTENDACE_DATE = '23-DEC-2017'
WHERE ED.MANAGER_ID = 222 
GROUP BY ED.MANAGER_ID, ED.EMP_ID;

here is:slqfiddle

Upvotes: 1

Suvam Roy
Suvam Roy

Reputation: 953

select EMP_ID,MANAGER_ID sum(NO_OF_SWIPEINS) as TODAY_SWIPEINS from EMPLOYEE_RECORDS group by EMP_ID;

You don't need to join two tables as the EMPLOYEE_RECORDS contains all data to manipulate. Now as you have no provision for Emp_ID 8888 so the final query would show nothing for 8888. For that you need include. So your EMPLOYEE_RECORDS table must look like this in order to get exact output as you mentioned.

EMP_ID MANAGER_ID NO_OF_SWIPEINS ATTENDACE_DATE
9999   2222         1             23-DEC-2017
7777   2222         1             23-DEC-2017
9999   2222         1             23-DEC-2017
9999   2222         1             23-DEC-2017
8888   2222         0             23-DEC-2017

Upvotes: 1

Vash
Vash

Reputation: 1787

You were joining only on MANAGER_ID and not on EMP_ID. Check the below improved query. 

SELECT edata.EMP_ID, edata.MANAGER_ID, sum(rec.NO_OF_SWIPEINS) as TODAY_SWIPEINS 
FROM 
EMPLOYEE_RECORDS rec right JOIN EMPLOYEE_DATA  edata
ON edata.MANAGER_ID = rec.MANAGER_ID and edata.EMP_ID = rec.EMP_ID
and rec.MANAGER_ID='2222' and trunc(rec.ATTENDANCE_DATE)='23-DEC-2017'
group by edata.EMP_ID ,edata.MANAGER_ID;

Upvotes: 0

Related Questions