Re: Pro*C COUNT -need help!
Date: 1996/08/08
Message-ID: <4ubrub$l92_at_inet-nntp-gw-1.us.oracle.com>#1/1
In article <4ubcrl$ppt_at_usenet.srv.cis.pitt.edu>, erlst3+_at_pitt.edu (Eric R Lapp) writes:
|> 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?
You could concatenate the fields together:
SQL> select count(distinct id || f_name || l_name) from ttt;
COUNT(DISTINCTID||F_NAME||L_NAME)
3
Since there is no subquery, Pro*C shouldn't have a problem.
|>
|> 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 **
|> ******************************************************************************
Scott Urman Oracle Corporation surman_at_us.oracle.com
Author of _Oracle PL/SQL Programming_ ISBN 0-07-882176-2 Published by Oracle Press - http://www.osborne.com/oracle/index.htm
"The opinions expressed here are my own, and are not necessarily that of Oracle Corporation"
Received on Thu Aug 08 1996 - 00:00:00 CEST