Mysql select with multiple total columns

Question

I have a table named sales with 5 fields:

id
date
id_store
id_customer
price

The table customers:

customers.id
customers.name

And the table stores:

stores.id
stores.name

I want to get this result:

January, Total customer name1, Total customer name2 ..., Total store name 1, Total store name 2...
February, Total customer name1, Total customer name2 ..., Total store name 1, Total store name 2...
March, Total customer name1, Total customer name2 ..., Total store name 1, Total store name 2...
April...

is it possible?

When you say "total customer name1", do you mean "total sales price of the customer name1"? If yes, you could use the "GROUP BY" statement mixed with the option "SUM". — antoox
– antoox, Commented May 6, 2013 at 16:31

Adrian · Accepted Answer · 2013-05-06 16:33:35Z

1

It is possible, using JOINs and WITH ROLLUP. Basically, you'll JOIN your tables together to get the data rows you need, then GROUP BY multiple columns (in your case, probably GROUP BY MONTH(sales.date), stores.id, customers.id WITH ROLLUP). Grouping by multiple columns gives you nested groups, in the order the columns are listed. WITH ROLLUP will give you summary data for each nesting level; so in my example, you'd get totals per customer per store per month, and totals per store per month, and overall totals per month.

answered May 6, 2013 at 16:33

Adrian

47.2k6 gold badges121 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Fermín Over a year ago

But show a column with total for each customer and each store?

Adrian Over a year ago

It will show totals for each customer by store & month, and for each store by month. If you want independent totals for each, you'll have to run independent queries.

Collectives™ on Stack Overflow

Mysql select with multiple total columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related