Re: newbie sql question

"Karsten Farell" <kfarrell_at_medimpact.com> wrote in message news:%hKm9.1531$x_2.152754369_at_newssvr13.news.prodigy.com...
> 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.
>
> Something (exactly) like this:
>
> select q.customerid, q.partid, count(*)
> from quotes q, workorders wo
> where q.partid = wo.partid
> and q.customerid = wo.customerid
> and to_number(to_char(q.datetimequoted,'yyyy')) =
> to_number(to_char(sysdate,'yyyy'))
> and wo.datetimeentered > q.datetimequoted
> group by q.customerid, q.partid
> order by q.customerid, q.partid;
>
>
> > However, I want a count and not the actual customer and part id.
> > wishful SQL:
> > select count(distinct q.partid, q.customerid)
> > .....
> >
>
> For your wishful SQL, you could do this (but I doubt that's what you're
> really looking for):
>
> select count(distinct q.partid || q.customerid)...
>
> And for the record, this doesn't sound like a homework assignment to me.
>
> -Karsten
>

Your input was truly useful, but not exactly what I was looking for ( although I am using it for something else) Your script produces the following output:

CUSTOM PARTID                COUNT(*)
------ -------------------- ---------
GEN013 4D98592                      4
PEN020 YC4-13750                    3

The script I ended up writing:

select count (*) from
 (select distinct q.customerid, q.partid   from quotes q, workorders wo
 where q.partid like wo.partid
 and q.customerid like wo.customerid
 and trunc(q.datetimequoted,'YEAR') = trunc(sysdate, 'YEAR'))  and wo.datetimeentered > q.datetimequoted)

Produces this:

 COUNT(*)

        2

which is what I was looking for. (the total number of entries that is produced in the CUSTOMER column of your output. I don't know if this is the most efficient sql but it works. If you have any idea of a better way to write it, I sure am open to any ideas. Thanks for your help Karsten, I appreciate it. Received on Thu Oct 03 2002 - 09:28:27 CDT