Re: beginner sql question

ksmith wrote:

> Orace 8i on Linux RH7.2
>
> I have two tables, a workorders table and a quotes table. I am trying to
> count all of the parts that are in the workorders table AND in the quotes
> table year to date. However they need to be distinct. For instance some
> parts are in the workorders table more than once, maybe even by two
> differnent customers. I can find the DISTINCT partid and customers using the
> following script:
>
> select distinct q.customerid, q.partid
> from quotes q, workorders wo
> where q.partid like wo.partid
> and q.customerid like wo.customerid
> and to_number(to_char(q.datetimequoted,'YYYY')) = to_number(to_char(sysdate,
> 'YYYY'))
> and wo.datetimeentered > q.datetimequoted
>
> This returns
>
> CUSTOM PARTID
> ------ --------------------
> GEN013 4D98592
>
> However, I want a count and not the actual customer and part id.
> wishful SQL:
> select count(distinct q.partid, q.customerid)
> .....
>
> Any help would be appreciated. As you can probably tell, I am far from an
> experience developer or dba. Thanks

Look in your textbook, or on the web at http://tahiti.oracle.com for examples with COUNT and GROUP BY.

Daniel Morgan Received on Wed Oct 02 2002 - 10:14:46 CDT