OraFAQ Forum: SQL & PL/SQL » Number of rows

Home » SQL & PL/SQL » SQL & PL/SQL » Number of rows

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Number of rows [message #10421]

Wed, 21 January 2004 11:30

Anurag Batra
Messages: 11
Registered: November 2002

Junior Member

I have a polling application where a user selects one of the options available to him....this is in 2 tables

Poll_Options

Poll_ID�� Poll_option

1��Red

1�� Blue

1�� Green

1�� Orange

�

the second table where I store the�options selected are�

Poll_results

Poll_id�� Userid �� Poll_option_selected

1��ABCD��Red

1�� EFGH��Blue

1�� IJKL�� Blue

�

Now�when I use�a normal group by query I donot get records for Green 0 and Orange 0 what�I get is�

Poll_id�� Poll_option_selected�� count(*)

1�� Red�� 1

1�� Blue��2

along with these records I want count(*) for Green and Orange as well��

Poll_id�� Poll_option_selected�� count(*)

1�� Red�� 1

1�� Blue��2

1��Green�� 0

1�� Orange�� 0

How do I do it?

�

thanks in advance

�

Report message to a moderator

Re: Number of rows [message #10424 is a reply to message #10421]

Wed, 21 January 2004 13:08

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Outer join:

sql>select po.poll_id, po.poll_option, count(pr.userid)
  2    from poll_options po, poll_results pr
  3   where pr.poll_id <b>(+)</b>= po.poll_id
  4     and pr.poll_option_selected <b>(+)</b>= po.poll_option
  5   group by po.poll_id, po.poll_option;
 
  POLL_ID POLL_OPT COUNT(PR.USERID)
--------- -------- ----------------
        1 Red                     1
        1 Blue                    2
<b>        1 Green                   0
        1 Orange                  0</b>
 
4 rows selected.

Report message to a moderator

Previous Topic:	ora-04052
Next Topic:	set up of remote DB

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 23:27:49 CDT 2024