SQL help

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/ Received on Wed Dec 13 2000 - 09:18:57 CST