CODEWITHSUNDEEP
sqloracle-databasejoin
Ninius86
Ninius86

Reputation: 295

How to count occurrences across two tables in Oracle?

I have two tables in Oracle 12c, goal is to count all occurrences in table2 which have Flag NULL, but also display 0 next to Names which are present in table1

table1

ID,Name
001,Bob
009,Alice
015,Bob
019,Bob
026,Alice
500,Rob
505,Rob

table2

ID,Flag,Timestamp
001,NULL,02/04/2016 16:33:13,991000
010,NULL,02/04/2016 16:33:14,991000
023,NULL,02/04/2016 16:33:15,991000
019,True,02/04/2016 16:33:16,991000
026,True,02/04/2016 16:33:17,991000
500,NULL,02/04/2016 16:33:18,991000
505,NULL,02/04/2016 16:33:19,991000

I'd like to get

Name,COUNT
Alice,0
Bob,1
Rob,2

My attempt so far is:

SELECT table1.Name, count(table1.Name) AS count
FROM table2
LEFT OUTER JOIN table1
ON table2.ID = table1.ID
WHERE table2.Flag IS null AND trunc(table2.Timestamp) = TRUNC(SYSDATE)
GROUP BY table1.Name

Returning

Name,COUNT
Bob,1
Rob,2

Upvotes: 2

Views: 593

Answers (2)

sagi
sagi

Reputation: 40491

You need to replace the position of table1 with table2 and count the id column of table2 (to exclude null values when the join condition is not met) .

SELECT table1.Name, count(table2.id) AS count
FROM table1
LEFT OUTER JOIN table2
ON table2.ID = table1.ID
 AND table2.Flag IS null
GROUP BY table1.Name

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

Try this instead:

SELECT table1.Name, count(table2.ID) AS count
FROM table1
LEFT OUTER JOIN table2 ON table2.ID = table1.ID
WHERE table2.Flag IS null
GROUP BY table1.Name

Upvotes: 1

Related Questions