Ninius86 Ninius86 - 5 months ago 12
SQL Question

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

Answer

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
Comments