Maude Rozencrance's Cat Maude Rozencrance's Cat - 4 months ago 8
SQL Question

mysql: two table join with sum

I'm attempting to join two tables and also get a SUM and flailing badly. I need to get the total commission amounts for each affiliate where affiliate.approved=1 AND order.status=3.

//affiliate table
affiliate_id | firstname | lastname | approved |
1 joe shmoe 1
2 frank dimag 0
3 bob roosky 1


here's the order table

//order
affiliate_id | order_status_id | commission
1 3 0.20
1 0 0.30
2 3 0.10
3 3 0.25
1 3 0.25
2 3 0.15
2 0 0.20


and here's what I'd like the query to return:

affiliate_id | commission
1 0.45
3 0.25


Here is my attempt that doesn't work. It outputs just one line.

SELECT order.affiliate_id, SUM(order.commission) AS total, affiliate.firstname, affiliate.lastname FROM `order`, `affiliate` WHERE order.order_status_id=3 AND affiliate.approved=1 AND order.affiliate_id = affiliate.affiliate_id ORDER BY total;


thanks for any help.

JPG JPG
Answer

You've missed GROUP BY, try this:

SELECT 
      `order`.affiliate_id,
      SUM(`order`.commission) AS total,
      affiliate.firstname,
      affiliate.lastname
FROM `order` 
JOIN `affiliate` 
ON `order`.order_status_id = 3 AND affiliate.approved = 1 AND `order`.affiliate_id = affiliate.affiliate_id
GROUP BY `order`.affiliate_id
ORDER BY total;

Demo Here

Comments