Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: SQL help
In article <91840q$lo6$1_at_nnrp1.deja.com>,
jwaterh_at_my-deja.com wrote:
> please excuse my ignorance, but could someone please give me some
> assistance.
>
> i am trying to query two tables, one called batches and the other
> invoices. Batches contains the name of the person who posted the batch
> and the batch number, amongst other things. Invoices has columns of
> batch number and purchase order. There can be many purchase orders
> associated with one batch number. I am trying to create a report that
> supplies the names of people who have posted batches, the number of
> that batch and a figure representing the number of invoices that are
> assossiated with each batch. Below is my attempt, which obviously does
> not work:
>
> select b.authorised_by, b.batch_ref, count (distinct i.invoice_no)
> from batches b, invoices i
> where b.batch_ref = i.bt_ref
>
> The error I get states "not a single-group group function", which I
> think relates to the fact that I am trying to reference multiple
> invoices into a single batch.
>
> Any help would be greatly appreciated.
>
> regards
>
> sean
>
> Sent via Deja.com
> http://www.deja.com/
>
add as last line
group by b.authorised_by, b.batch_ref
and it will work
Hth,
-- Sybrand Bakker, Oracle DBA All standard disclaimers apply ------------------------------------------------------------------------ Sent via Deja.com http://www.deja.com/Received on Wed Dec 13 2000 - 09:35:29 CST