Help with DB query (MySQL)

techguy

I need some help with a query that will get all the data in one table (customer) then in another table (transactions) sum the column 'nett_weight' for each different product (pid) with the specified customer(cid).

I am currently using a standard join and some subqueries, 1 for each product to be counted. I seem to have it working but it returns many rows. I want it to return only one row with a column for each product total.

Here's what I have:

SELECT *,
(SELECT SUM(transaction.nett_weight) where pid ='1') as Product1,
(SELECT SUM(transaction.nett_weight) where pid ='2') as Product2
JOIN transaction ON customer.cid = transaction.ci
FROM customer
WHERE cid = '9'

I'm away from my main computer now so I can't test this exact query. ATM it sums all weights for given product from all customers not just the specific customer.

I suppose I should probably include sample tables, we'll see how it goes.

Thanks..

showry

Assuming you have a finite number of products the following should work

mysql> select * from trans;
+

---

+

---

+

---

+
| cid | pid | weight |
+

---

+

---

+

---

+
| 1 | 1 | 4.00 |
| 1 | 1 | 5.00 |
| 1 | 2 | 3.00 |
| 1 | 2 | 10.00 |
| 2 | 1 | 1.00 |
| 2 | 1 | 5.00 |
| 2 | 2 | 5.00 |
| 2 | 2 | 7.00 |
| 3 | 1 | 7.00 |
| 3 | 1 | 2.00 |
| 3 | 2 | 2.00 |
| 3 | 2 | 14.00 |
+

---

+

---

+

---

+
12 rows in set (0.00 sec)

mysql>
select name,
sum(case when pid = 1 then weight else 0 end) p1,
sum(case when pid = 2 then weight else 0 end) p2
from cust, trans
where cust.cid = trans.cid
group by name
order by cust.cid;
+

---

+

---

+

---

+
| name | p1 | p2 |
+

---

+

---

+

---

+
| Kerry Dixon | 9.00 | 13.00 |
| David Speedie | 6.00 | 12.00 |
| Pat Nevin | 9.00 | 16.00 |
+

---

+

---

+

---

+
3 rows in set (0.01 sec)

mysql>

techguy

Thanks showry,

I haven't had a chance to look into this yet. Will do so next week when I get back to more regular hours..