Daniel Morgan wrote:
> 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
>
and just to toss in my 2cents (take this as a hint ksmith):
I think DISTINCT is the most misused feature of SQL.
It tends to hide logic errors. And often, even a quick query can use a
COUNT(*) instead to gain a slight bit more information at no greater
performance cost.
just my opinion.
Ed Prochak
Received on Thu Oct 03 2002 - 15:21:14 CDT
