I have 3 tables:
Customers: Items: Purchases: id id id first_name name customer_id last_name price item_id
I need to present data in a way that a can see all the purchases of a customer, per item, the amount (sum) of each item and the total price of this amount(again, per item), like this:
Name: | Last Name: | Item: | Price: | Amount: | Total: John | Doe | Pencil | 0.5 | 5 | 2.5 John | Doe | Paper(A4) | 3.0 | 20 | 60.00 Michael | Smith | Pencil | 0.5 | 3 | 1.5 Michael | Smith | Paper(A4) | 3.0 | 10 | 30.00
I manage to do that with this query:
SELECT
customers.first_name AS "Name",
customers.last_name AS "Last Name",
items.name AS "Item",
items.price AS "Price",
COUNT(items.id) AS "Amount",
SUM(items.price) AS "Total"
FROM
items
INNER JOIN
purchases ON items.id = purchases.item_id
INNER JOIN
customers ON purchases.customer_id = customers.id
GROUP BY
customers.id, items.id
ORDER BY customers.last_name
Is there a way to do it like you would in Excel, for example, where you can merger those cells that have the first and last name, so the customer name is listed only once? I now that it is not the most relevant "problem", but I have to do this twice a week in a meeting with 5 other people and it would be great to improve this layout.