CODEWITHSUNDEEP
sqljoinsum
user3192682
user3192682

Reputation: 123

sum total spent by customers from certain manufacturer including $0

I have an SQL database with three corresponding tables, customers, orders, and items. Customers have an id which corresponds to one in orders. orders has an order number which corresponds to one in items. Items is the table which includes the amount customers spent at a specific company. In this case, I want to sum the total spent at a company called, "HRO" INCLUDING those customers that didn't spend any at HRO (with a zero next to their name). I have this working correctly except for the including zero part. Thanks in advance!

select customer.customer_num, customer.fname, sum(items.total_price) amountSpent
from customer
    left join orders
        on customer.customer_num = orders.customer_num
    left join items
        on orders.order_num = items.order_num
where items.manu_code like 'HRO'
group by customer.customer_num, customer.fname

Upvotes: 1

Views: 390

Answers (1)

Blorgbeard
Blorgbeard

Reputation: 103525

Your where clause items.manu_code like 'HRO' is excluding customers that don't have any orders, since those customers' rows will have null for items.manu_code.

Instead of the where clause, try moving that filter into the join:

select 
     customer.customer_num, customer.fname, 
     isnull(sum(items.total_price),0) amountSpent
from customer
    left join orders
        on customer.customer_num = orders.customer_num
    left join items
        on orders.order_num = items.order_num 
       and items.manu_code like 'HRO'
group by customer.customer_num, customer.fname

I've wrapped the sum in an isnull so that customers with no orders show up as zero spent instead of null spent.

Upvotes: 3

Related Questions