SQL question 2

[dbadmin]

Hi,

I have a customer table with related tables (invoice, products etc). All these related tables have customer_id column as Foreign key. I need a query which will give me counts of invoices, products etc corresponding to each customer id for example like this

cust_id count(inv) count(prod)
100 10 40
101 2 10
102 22 25

I tried to join these tables and group by customer_id, but I am not getting the correct answer. How could I do this?

dbadmin

 

[dsanchez2]

Try the following:

select c.cust_id,i.inv,p.prod
from customer c,
  (select cust_id,count(*) inv from invoice group by cust_id) i,
  (select cust_id,count(*) prod from product group by cust_id) p
where c.cust_id = i.cust_id
and c.cust_id = p.cust_id

 

[Dagon]

You might need to use outer joins to pick up rows where there are no invoices and/or poducts for a customer.

Another possibility:

select c.customer_id, 
       count(distinct p.product_id),
       count(distinct i.invoice_id)
from customer c, invoice i, product p
where c.customer_id = p.customer_id(+)
and   c.customer_id = i.customer_id(+)
group by c.customer_id

 

[dbadmin]

Hi,

Thank you. I got what I was looking for. I have to thank both of you Dagon and dsancez2.

dbadmin