Re: Pro*C COUNT -need help!(B

From: Ken Mizuta(B <kmizuta_at_jp.oracle.com>
Date: 1996/08/07
Message-ID: <32096412.F11_at_jp.oracle.com>#1/1

Eric R Lapp wrote:
>
> I need to do a count that would do the following:
>
> assuming tables:
>
> A
> ID F_Name L_Name Num
> ---------------------
> 1 A B 3
> 1 A A 2
> 1 A C 6
> 1 A B 1
>
> I want to get back a 3 in this situation.
> i.e. where distinct ID, F_Name, and L_Name
>
> How do I do a count that would produce the desired results?
>
> SELECT COUNT(DISTINCT ID, F_NAME, L_Name)
> FROM A
> WHERE ID = 1;
>
> I guess after I do the WHERE ID = 1 all that I would need is a count of the
> distinct combinations of F_Name and L_Name. I know the above DISTINCT is
> wrong...
>
> I am able to do this in SQL*Plus, but NOT in the Pro*C:
>
> SELECT COUNT(*)
> FROM (SELECT DISTINCT F_Name, L_Name FROM A WHERE ID = 1);
>
> This (I think works), but Pro*C doesn't allow a subquery in the FROM clause.
>
> If anyone can help that would be GREAT!!!! TIA
>
> --
> ******************************************************************************
> ** Eric R. Lapp E-Mail: erlst3+_at_pitt.edu **
> ******************************************************************************

The following should work for the above case:

SELECT COUNT(*) FROM A

	WHERE ID=1
	GROUP BY ID, F_NAME, L_NAME;


-- 
                  ____________________________________________ 
                 /    Kenichi Mizuta 
      ////      /     Oracle Corporation (Redwood Shores, CA) 
     |0 0|     /      Applications Division 
_ooO_ \U/_Ooo_/       email: kmizuta_at_us.oracle.com 

The comments and opinions expressed herein are mine and do not necessarily represent those of Oracle Corporation. Received on Wed Aug 07 1996 - 00:00:00 CEST