Home » SQL & PL/SQL » SQL & PL/SQL » help with COUNT function (sql developer 1.5.1)
help with COUNT function [message #351576] Wed, 01 October 2008 11:02 Go to next message
anthony_dcp
Messages: 3
Registered: October 2008
Junior Member
Hello. I'm creating a table about hockey players and I'm trying to COUNT the number of games in which the players have at least one goal or one pass.

SELECT pl.name,count(sc.nbgoals+sc.nbpasses)
FROM player Pl, score Sc
WHERE pl.noPlayer = sc.noPlayer (+)
GROUP BY pl.name
ORDER BY pl.name
;

What it gives me right now are the games in which they have at least one goal AND one pass, how can I code it so it will count a game that a player only has, for example, one pass?

Thanks!
Re: help with COUNT function [message #351580 is a reply to message #351576] Wed, 01 October 2008 11:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'll guess that nbgoals and nbpasses are set to null rather than zero be default.
If that's true, you need to do
COUNT(nvl(nbgoals,0) + nvl(nbpasses,0))

This is because COUNT only counts non-null values, and Number + Null = Null
Re: help with COUNT function [message #351585 is a reply to message #351576] Wed, 01 October 2008 11:38 Go to previous messageGo to next message
anthony_dcp
Messages: 3
Registered: October 2008
Junior Member
when i use nvl it returns the number of games each player has played, strangely

a player that has neither a goal or a point will have the value one

this is because 0 is not null and consequently it is counted?


Re: help with COUNT function [message #351586 is a reply to message #351585] Wed, 01 October 2008 11:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to use SUM and not COUNT.
COUNT counts the rows with not null value, SUM sums the (not null) values.

Regards
Michel
Re: help with COUNT function [message #351589 is a reply to message #351585] Wed, 01 October 2008 11:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Good point - not enough coffee over here, obviously.

Try
COUNT(to_char(nbgoals)||to_char(nbpasses))

or
SUM(nvl2(nbgoals,1,0)+nvl2(nbpasses,1,0))
Re: help with COUNT function [message #351631 is a reply to message #351589] Wed, 01 October 2008 15:33 Go to previous message
anthony_dcp
Messages: 3
Registered: October 2008
Junior Member
thanks a lot JRowbottom! your COUNT function worked like a charm Smile
Previous Topic: Java Developer learning PL/SQL
Next Topic: Materialized View
Goto Forum:
  


Current Time: Sat Dec 10 14:41:23 CST 2016

Total time taken to generate the page: 0.12272 seconds