SQL/SQL-LITE - Counting records after filtering

Question

Suppose I have a table of customers and a table of sales order with the following schemas:

1. Customer = {id, name}
2. Sales_order = {id, customer_id, sales_representer}

With the following defintions :

1. id is a primary key in both tables.
2. customer_id is a foriegn key references customer.

I want to implement the following query :

For any customer whose sales_representer is 100, find the customer id, 
customer name and the number of his overall orders.

I built the following query:

select C.id, C.name, count(C.id)
from customer C, sales_order S
where   C.id = S.customer_id and 
        S.sales_represntor = '100'
group by C.id, C.nname;

But as a result of count(C.id) I get only the number of sales whose the sales_representer is 100. I know I can add another instance of sales_order (i.e. S2) and count from it but It seems to me not efficent at all.

Do anyone have a solution ?

Thank you

Answer 1

You could use a correlated subquery to calculate the sales number. (In SQLite, subqueries are often as efficient as a join.)

SELECT id,
       name,
       (SELECT COUNT(*)
        FROM sales_order
        WHERE customer_id = customer.id) AS orders
FROM customer
WHERE id IN (SELECT customer_id
             FROM sales_order
             WHERE sales_representer = '100')

If you care about efficiency, you should check the queries with EXPLAIN QUERY PLAN, or even better, just measure them.

Answer 2

You could use a having clause to demand that at least one sale was by representative 100:

select  C.id
,       C.name
,       count(*) as TotalSaleCount
from    customer C
join    sales_order S
on      C.id = S.customer_id
group by 
        C.id
,       C.name
having  count(case when S.sales_representor = '100' then 1 end) > 0