I have three tables, products, customers, order
Product:
id | name |
1 | milk |
2 | bread|
3 | Pea |
Customer:
id | name | category
1 | James | retailer
2 | Paul | vendor
3 | Dave | retailer
Order:
id | product_id | customer_id | qty | price
1 | 1 | 2 | 23 | 50
2 | 2 | 2 | 4 | 30
3 | 3 | 2 | 6 | 10
4 | 2 | 1 | 9 | 30
5 | 3 | 1 | 2 | 10
6 | 1 | 3 | 6 | 50
7 | 3 | 3 | 7 | 10
When i do a query to show transactions by customers with category of vendor like
SELECT customer.name, product.name as pname, order.qty, order.price FROM customer, product, order
WHERE customer.id = order.customer_id
AND product.id = order.product_id AND customer.category = "vendor"
i will get something like:
name | pname | qty | price
Paul | milk | 23 | 50
Paul | bread | 4 | 30
Paul | pea | 6 | 10
I want this instead:
name | milk | bread | pea | total
Paul | 23 | 4 | 6 | 90
While that of retailers will look like this:
SELECT customer.name, product.name as pname, order.qty, order.price FROM
customer, product, order
WHERE customer.id = order.customer_id
AND product.id = order.product_id AND customer.category = "retailer"
I will get a table like this:
name | pname | qty | price
James | bread | 9 | 30
James | pea | 2 | 10
Dave | milk | 6 | 50
Dave | pea | 7 | 10
But i want this instead:
name | milk | bread | pea | total
James | 0 | 9 | 2 | 40
Dave | 6 | 0 | 7 | 60
Simply use conditional aggregation for pivoting columns. And be sure to use explicit joins instead of the deprecated implicit join as former has been the standard for 25 years in ANSI-92.
SELECT c.name,
SUM(CASE WHEN p.name = 'milk' THEN o.qty ELSE 0 END) as milk,
SUM(CASE WHEN p.name = 'bread' THEN o.qty ELSE 0 END) as bread,
SUM(CASE WHEN p.name = 'pea' THEN o.qty ELSE 0 END) as pea,
SUM(o.price) AS Total
FROM `customer` c
INNER JOIN `order` o
ON c.id = o.customer_id
INNER JOIN `product` p
ON p.id = o.product_id
WHERE c.category = 'vendor' -- same for retailer
GROUP BY c.name
I think that you cannot have this response structure directly from one simple select
name | milk | bread | pea | total
James | 0 | 9 | 2 | 40
Dave | 6 | 0 | 7 | 60
because your database is getting one row foreach retailers/customer order.
I know that using a server language like PHP or Java you will can handle the data and retrive like you want.