Sum on multi-joined tables

Question

I have 4 tables

customers
sales
sale_items
stock_items

I want to list all my customers. For each customer I want the total amount purchased - a field that I want the sql query to create (xxx as totalSales)

I tried selecting customers as the primary table and joining other tables on it. I tried selecting sales as the primary table and also the sale_items. I kind of got the correct sum calculation but it will show me the first customer first.

This is what my tables look like:

*TABLE customers*
id
customer_name
email

*TABLE sales*
id
customer_id
transaction_date

*TABLE sale_items*
id
sale_id
stock_item_id

*TABLE stock_items*
id
item_name
price

I want to

Create a list of all customers, sorted by the customer with the most sales (in value) first. Not the count of the sales, but the total amount (sales value) of the sales
Display the items purchased per customer under the customer name. This would not be per order, but for all sales. So if a tin of coffee was purchased by customer X, over a count of 4 orders each, the tin of coffee would display 4 times. Though if possible I'd like the items listed a-z.

I have inner joined the tables on each other, so I would get a list of all transactions. I've tried SELECT * FROM customers, tried FROM sales, tried from sale_items. I've tried GROUP_BY customer_id, but then I would get incorrect counts.

I want to display the data as such.

CUSTOMER                    ITEMS                   TOTAL VALUE
John Doe                    Coffee
                            Milk
                            Tea
                            Milk
                            Bread
                            Coffee                  500

Jane Doe                    Coffee
                            Milk
                            Coke                    350

Denver Doe                  Coffee
                            Milk
                            Bread
                            Bread                   125

I don't want to use PHP and make a query for every single "thing". I am trying to run one or two queries.

Please check https://stackoverflow.com/questions/39550294/query-to-find-the-customer-with-highest-amount-of-total-purchases — Rahul, May 29 '18 at 17:02

score 1 · Answer 1 · answered May 29 '18 at 17:00

I don't think you're going to be able to quite get them in separate rows like you're showing (unless you look into the ROLLUP function), but that's probably not a requirement for you.

I think you should use GROUP_CONCAT which is an aggregation function sort of like SUM but it creates a comma separated list out of all of the values:

SELECT 
*
SUM(sale_amount) as total_sales,
GROUP_CONCAT(item_name) as item_names
FROM customers c
JOIN sales s USING (customer_id)
JOIN sale_items si USING (sale_id)
JOIN stock_items sit USING (stock_item_id)
GROUP BY customer_id

What you should see as a sample row is:

Denver Doe     Coffee,Milk,Bread,Bread     125

(I had to make up some column names, like sale_amount, but you must have those in there I'm sure. You'll have to make some adjustments in those names and maybe in how I did the joins as well, but this should work with some changes if I'm understanding your needs).

Sum on multi-joined tables

1 Answers1