Home » SQL & PL/SQL » SQL & PL/SQL » SQL GROUP BY (Oracle 9i)
SQL GROUP BY [message #355069] Wed, 22 October 2008 08:13 Go to next message
piduruviswa
Messages: 12
Registered: May 2007
Location: Singapore
Junior Member
Hi,

I'm bit off in framing a SQL for one of requirement. Pls help.

Say I have a table foo in which I want to get counts for one of the column

For example: I have "x" column in foo table where I pass values like x in (1,2,3,4,5) So if the value exists in the table for x the respective count should come else the value should be printed with count 0.

sample o/p:

foo

x
1
1
2
3
4
1
4
5

so in the select statement where clause if I give where x in(1,2,5,7,6) then o/p should be

x count
1 3
2 1
5 0
7 0
6 0
Re: SQL GROUP BY [message #355070 is a reply to message #355069] Wed, 22 October 2008 08:18 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
I see 5 in there, so the description of your problem makes no sense. Try again. Additionally, do not expect the order to show in the order that you put in the WHERE clause because it will not.

[Updated on: Wed, 22 October 2008 08:19]

Report message to a moderator

Re: SQL GROUP BY [message #355073 is a reply to message #355069] Wed, 22 October 2008 08:27 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@piduruviswa,

Please post what you have tried.

*** Hints:
OUTER JOINS
GROUP BY Clause
HAVING Clause
COUNT Function
NVL Function (***Forgot this one)
I guess you have to generate the records that you pass in the IN Function and join it(use outer joins) to your table.

Hope this helps.

(Besides, the sample output you gave is wrong. There is a value 5 in your column... http://img2.mysmiley.net/imgs/smile/innocent/innocent0002.gif)

Regards,
Jo

[Updated on: Wed, 22 October 2008 08:34]

Report message to a moderator

Re: SQL GROUP BY [message #355074 is a reply to message #355070] Wed, 22 October 2008 08:30 Go to previous messageGo to next message
piduruviswa
Messages: 12
Registered: May 2007
Location: Singapore
Junior Member
hi,

thanks for your prompt response.

Actually it is a group by on column x

select count(*),x from foo where x in(1,2,5,7,6) group by x;

Now as u said I wont be getting the x values which is not available in table. but I need them in the o/p

I was thinking of writing procedure but I'm not sure if we can write something where it checks all the values with out doing a loop. The reason why I dont want to do a loop is I have lot of values like that.

Not sure if it makes sense for you this time.

Pls let me know if something is not clear.
Re: SQL GROUP BY [message #355078 is a reply to message #355073] Wed, 22 October 2008 08:37 Go to previous messageGo to next message
piduruviswa
Messages: 12
Registered: May 2007
Location: Singapore
Junior Member
Thanks John,

Appreciate your help. Will try that way...

I agree that the count is 1 for value 5. It was a typo

[Updated on: Wed, 22 October 2008 08:38]

Report message to a moderator

Re: SQL GROUP BY [message #355080 is a reply to message #355074] Wed, 22 October 2008 08:47 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@piduruviswa,

One of the way to generate the values will be like: (Just a sample)

SQL> SELECT     LEVEL
  2        FROM DUAL
  3       WHERE LEVEL IN (1, 3, 5)
  4  CONNECT BY LEVEL <= 10;

     LEVEL
----------
         1
         3
         5


Join the resultset from the above query with your table as I mentioned before.

Regards,
Jo
Previous Topic: query to find the values which are not in IN clause
Next Topic: xmlelement
Goto Forum:
  


Current Time: Mon Dec 05 20:59:50 CST 2016

Total time taken to generate the page: 0.09403 seconds