I have a relational db with 3 tables. The first one, Categories has the type of items of a store like veggies, meat, cheeses. The second one, Clients has all the clients that had bought in the store. The last one, Cart, has a relation with the client id, category id and the amount of money a client spent in the store.
I need to print a report, using MySQL to know how much spent each client in a category. The report should look like
+--------+---------+------+---------+
| Client | Veggies | Meat | Cheeses |
+--------+---------+------+---------+
| Tom | 1000 | NULL | 1500 |
| John | NULL | NULL | 2000 |
| Alice | 1000 | 1000 | 1000 |
+--------+---------+------+---------+
Right now, I'm using the following query
SELECT client.name AS Client,
category.name AS Category,
AVG(cart.subtotal) AS Total
FROM cart
INNER JOIN category ON category.id = cart.id_category
INNER JOIN clients ON clients.id = cart.id_client
GROUP BY cart.id_client,
cart.id_category
But I get this:
+--------+----------+-------+
| Client | Category | Total |
+--------+----------+-------+
| Tom | Veggies | 1000 |
| Tom | Cheeses | 1500 |
| John | Cheeses | 2000 |
| Alice | Veggies | 1000 |
| Alice | Meat | 1000 |
| Alice | Cheeses | 1000 |
+--------+----------+-------+
