Home » SQL & PL/SQL » SQL & PL/SQL » QUERRY Help (ORACLE v9.2.0.5.0 PL/SQL v7.0.3.1123)
QUERRY Help [message #353037] Fri, 10 October 2008 13:21 Go to next message
diabboliq
Messages: 2
Registered: October 2008
Location: Bucharest
Junior Member

Hello forum friends.
This is my first time here, also my fist post. From the beggining i apologize for any mistake in my writeing.
So, this is my problem:
User1 has 10 cars
User2 has 3 cars
User3 has 15 cars
User4 has no cars [zero].

When i do a select on this to count the cars of every user the result will be like this one:

User count(cars)
user1 10
user2 3
user3 15

What should i write to have a resoult like this:

user1 10
user2 3
user3 15
user4 0

I hope i was clear enought..if not, please feel free to ask me everything you want.

Thank you,
Alex.
Re: QUERRY Help [message #353044 is a reply to message #353037] Fri, 10 October 2008 13:58 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It seems that User 4 doesn't have "zero" cars (as you've put it), but "NULL" cars. As I don't know your model, I guess that NVL function might solve the problem (such as SELECT NVL(number_of_cars, 0) ...). On the other hand, outer join might add some light to the problem.
Re: QUERRY Help [message #353050 is a reply to message #353037] Fri, 10 October 2008 14:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
This is my first time here, also my fist post. From the beggining i apologize for any mistake in my writeing.

To fix that please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

In the end, post a test case: create table and insert statements along with the result you want with these data and use SQL*Plus and copy and paste your session to show what you tried.

Regards
Michel
Re: QUERRY Help [message #353053 is a reply to message #353037] Fri, 10 October 2008 15:04 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
diabboliq wrote on Fri, 10 October 2008 14:21

So, this is my problem:
User1 has 10 cars
User2 has 3 cars
User3 has 15 cars
User4 has no cars [zero].

When i do a select on this to count the cars of every user the result will be like this one:

User count(cars)
user1 10
user2 3
user3 15

What should i write to have a resoult like this:

user1 10
user2 3
user3 15
user4 0



What about user79, doesn't they also have 0 cars?

[this is actually a nudge or a hint, not a sarcastic remark]
Re: QUERRY Help [message #353054 is a reply to message #353050] Fri, 10 October 2008 15:05 Go to previous messageGo to next message
diabboliq
Messages: 2
Registered: October 2008
Location: Bucharest
Junior Member

Thank you for the advice Michael. I`ll do so.

For now, it`s not necesary to post the code. I`ll make another example Smile The code is to big Very Happy

Name Date Cars Bought
Agent1 10.10.2008 1
Agent1 10.10.2008 3
Agent1 10.10.2008 15
Agent1 08.10.2008 10
Agent2 10.10.2008 12
Agent2 10.10.2008 2
Agent2 09.10.2008 3
Agent3 09.10.2008 12
Agent3 08.10.2008 3

The SUM will be like this:

Agent1 on 10.10.2008 bought 1+3+15=19
Agent2 on 10.10.2008 bought 12+2=14
Agent3 on 10.10.2008 bought NOTHING

Well, when i wanna querry how many cars bought every agent on the 10.10.2008 date, the pl/sql will return like this

Agent_Name Date Sum(Cars)
Agent1 10.10.208 19
Agent2 10.10.208 14

I wanna have also Agent3 in the result of the querry with 0 cars. Like this:

Agent_Name Date Sum(Cars)
Agent1 10.10.208 19
Agent2 10.10.208 14
Agent3 10.10.208 0


Is it now clear enought ?

Thank you all,
Alex.
Re: QUERRY Help [message #353055 is a reply to message #353054] Fri, 10 October 2008 15:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Thank you for the advice Michael. I`ll do so.

Do it NOW!
Michel Cadot wrote on Fri, 10 October 2008 21:25
To fix that please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

In the end, post a test case: create table and insert statements along with the result you want with these data and use SQL*Plus and copy and paste your session to show what you tried.

Regards
Michel


Re: QUERRY Help [message #353058 is a reply to message #353044] Fri, 10 October 2008 15:33 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@diabboliq,

Littlefoot wrote on Sat, 11 October 2008 00:28
It seems that User 4 doesn't have "zero" cars (as you've put it), but "NULL" cars. As I don't know your model, I guess that NVL function might solve the problem (such as SELECT NVL(number_of_cars, 0) ...). On the other hand, outer join might add some light to the problem.


As suggested by Littlefoot use OUTER JOINS and NVL Function.

Regards,
Jo

Previous Topic: index help
Next Topic: SQL Mass update - dumb question
Goto Forum:
  


Current Time: Fri Dec 09 19:16:29 CST 2016

Total time taken to generate the page: 0.13508 seconds