CODEWITHSUNDEEP
sqldatabaseoracle-database
MiZi
MiZi

Reputation: 3

Count with joins

I have three tables:

User
ID

ImportFile
ID
reportingUser_id

Activity
ID
importFile_id
date

I'm searching for a query which answers the following question:
How many ImportFiles with activities in one month does every user uploaded.

For Example:
User
1
2

ImportFile
1, 1
2, 1
3, 2

Activity
1, 1, 01.01.2018
2, 1, 15.01.2018
3, 2, 02.01.2018
4, 3, 02.01.2018

Expected Result:
User.id, count(importFiles), trunc(date, 'MM')
1, 2, 01.01.2018
2, 1, 01.01.2018

My (incorrect) query:

SELECT user.id, count(a.importfile_id), trunc(a.date, 'MM') FROM activity a 
left join importfile imp on imp.id = a.importfile_id 
left join user on user.id = importfile.reportingUser_id
group by trunc(a.date, 'MM'), a.importFile_id, user.id

Upvotes: 0

Views: 74

Answers (4)

vishnudattan
vishnudattan

Reputation: 476

As discussed in comments, the original post was missing data in ImportFile table for User 2. Once that is fixed, you'll just need to modify your original query to have count(distinct a.importfile_id) and group by trunc(a.date, 'MM'), user.id

I've added a demo here

Upvotes: 0

Hacı Celal Aygar
Hacı Celal Aygar

Reputation: 518

SELECT * FROM ( 
    SELECT
        T1.ID AS USER_ID,
        COUNT(T3.importFile_id) AS CNT,
        TO_CHAR(TRUNC(t3.date, 'MM'), 'YYYY-MM-DD') AS DATEE
    FROM USERR T1
    FULL JOIN ImportFile T2 ON T1.ID = T2. reportingUser_id
    FULL JOIN Activity T3 ON T3.importFile_id = T2. ID
    GROUP BY TRUNC(t3.date, 'MM'), T1.ID )
ORDER BY USER_ID

Upvotes: 0

E. L.
E. L.

Reputation: 522

I think your query needs to changes: 1.) count(distinct a.importfile_id) instead of count (a.importfile_id) as a file may have multiple activities 2) as what @Gordon said “ GROUP BY trunc(a.date, 'MM'), u.id;”

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I think you just need to fix the GROUP BY and use COUNT(DISTINCT):

SELECT u.id, count(distinct a.id),
       trunc(a.date, 'MM')
FROM user u join
     importfile i
     on u.id = i.reportingUser_id join
     activity a 
     on i.id = a.importfile_id 
GROUP BY trunc(a.date, 'MM'), u.id;

I changed the JOINs. Each activity should have proper import files and users, so outer joins don't seem necessary.

Upvotes: 2

Related Questions