Oracle Count not showing 0

Question

I'm having a little problem with the following query:

SELECT 
    country.country_id AS "COUNTRY_ID", count(con.medal_id) AS "GOLD"
FROM
    country country 
LEFT OUTER JOIN
  contestant con on con.country_id = country.country_id
AND con.medal_id = 1
LEFT OUTER JOIN 
  event e on e.event_id = con.event_id 
WHERE  e.og_id = 1 
GROUP BY
    country.country_id
ORDER BY
    country.country_id

I'm trying to get my query to display as the following:

COUNTRY_ID       GOLD
---------- ----------
         1          1 
         2          2 
         3          0 
         4          0

However, with my current query, it is showing this:

COUNTRY_ID       GOLD
---------- ----------
         1          1 
         2          2

If i change the WHERE e.og_id = 1 into AND e.go_id = 1

The results will be the following:

COUNTRY_ID       GOLD
---------- ----------
         1          1 
         2          5 
         3          0 
         4          3

This result is same as not having this line:

LEFT OUTER JOIN 
  event e on e.event_id = con.event_id 
AND  e.og_id = 1

If anyone needs more info, please let me know.

Thank you for your help.

I would really like to give both of the people a tick, however, because I got the answer by modifying Frank's code, so I'll give him the tick. Thanks again for all the help — fireboy0526, Jan 27 '14 at 14:15

score 2 · Accepted Answer · answered Jan 27 '14 at 11:46

Moving the clause e.og_id = 1 to the JOIN should do the trick:

SELECT 
    country.country_id AS "COUNTRY_ID", count(con.medal_id) AS "GOLD"
FROM
    country country 
LEFT OUTER JOIN contestant con 
  on con.country_id = country.country_id AND con.medal_id = 1
LEFT OUTER JOIN event e 
  on e.event_id = con.event_id AND e.og_id = 1 
GROUP BY
    country.country_id
ORDER BY
    country.country_id

Alternatively, you could explicitly allow NULLs:

SELECT 
    country.country_id AS "COUNTRY_ID", count(con.medal_id) AS "GOLD"
FROM
    country country 
LEFT OUTER JOIN contestant con 
  on con.country_id = country.country_id AND con.medal_id = 1
LEFT OUTER JOIN event e 
  on e.event_id = con.event_id 
WHERE e.og_id = 1 or e.og_id IS NULL
GROUP BY
    country.country_id
ORDER BY
    country.country_id

I have tried using AND for `e.og_id`, however that didn't work. I've tried your second way of doing it, but it is only showing country 1,2,3 but not 4.Any possible ways that I can fix it? if you need more info please let me know. — fireboy0526, Jan 27 '14 at 12:10
After spending some time modifying your code, I have finally got it to work. — fireboy0526, Jan 27 '14 at 14:14

score 1 · Answer 2 · edited May 23 '17 at 11:49

1

There WHERE will filter out null values created by the left join. Move the where into the join condition:

SELECT 
    country.country_id AS "COUNTRY_ID", count(con.medal_id) AS "GOLD"
FROM
    country country 
      LEFT OUTER JOIN contestant con 
        on con.country_id = country.country_id
        AND con.medal_id = 1
     LEFT OUTER JOIN 
       event e on e.event_id = con.event_id 
       AND e.og_id = 1 
GROUP BY
    country.country_id
ORDER BY
    country.country_id;

Or alternatively, move the filters back into the where clause, handling the case where the LEFT JOIN returns NULLs.

    country country 
      LEFT OUTER JOIN contestant con 
        on con.country_id = country.country_id
     LEFT OUTER JOIN 
       event e on e.event_id = con.event_id 
   WHERE 
       (con.country_id IS NULL OR con.medal_id = 1)
       AND 
       (e.event_id IS NULL OR e.og_id = 1)

More about this behaviour here

edited May 23 '17 at 11:49

Community

1
1

answered Jan 27 '14 at 11:47

StuartLC

104,537
17
209
285

Hey there, thanks for your reply. I've tried both ways, and as answering the guy up above, the first one didn't work. It still won't take the `e.og_id` part into the query. The second one will only show country 1,2,3 but not 4. Is there any other way to fix it? Do you need me to provide any more info? – fireboy0526 Jan 27 '14 at 12:11
The 2 queries should be equivalent. I've added a [SqlFiddle here](http://sqlfiddle.com/#!4/69127/4) with some test data with the edge cases I can think of and it seems to work. Thoughts? – StuartLC Jan 27 '14 at 12:30
I'm using oracle sql developer, for some reason, i think the code should work too. I'm not understanding why it can call 3 countries and not the last one.... And yes, your example are good enough. Also, I don't get why moving the where into the JOIN doesn't do the trick...moving it into JOIN looks like that JOIN query is just being ignore... – fireboy0526 Jan 27 '14 at 12:54
I've just tried to move that code into my bigger code. From the output, it seems like instead of calling country 4, it is leaving a huge blank block of space instead..... And if joining with other table, country 4 will appear bellow the blank block. This is wierd – fireboy0526 Jan 27 '14 at 13:00
I'm not sure if it is an oracle thing, cause it doesn't seem to have any problem with fiddle. I just used the table u've created though, cause fiddle can't understand oracle's statement – fireboy0526 Jan 27 '14 at 13:26
I'm able to reproduce where it shows only 2 [link](http://sqlfiddle.com/#!2/1e098b/5) – fireboy0526 Jan 27 '14 at 13:40
You've changed the table order. T1 LEFT JOIN T2 is not the same as T2 LEFT JOIN T1 :). T1 must be the table from which you want ALL rows to be returned irrespective of the joins to other tables, in your case, country. – StuartLC Jan 27 '14 at 13:59
it's ok, i have it solved. thanks alot for all the help you've done. – fireboy0526 Jan 27 '14 at 14:21

Oracle Count not showing 0

2 Answers2