0

Suppose I have table with sales information for different stores that sell fruit, like this:

Store Fruit Sale
One apples 2
Two oranges 5
One pears 3
Three apples 2
One oranges 3
Three apples 4
Two apples 1
One pears 5

Is there a way to write SQL query, that would return table with sum of each fruits sold for each store, something like:

Store apples oranges pears
One 2 3 8
Two 1 5 0
Three 6 0 0

Or must I run 3 different queries, one for each store? Thanks.

Moulitharan M
  • 729
  • 4
  • 15
Aleš
  • 79
  • 1
  • 9
  • depends on your dbms. You can do a group, but to get each possible value as its own column is non-standard. Possible on MS-SQL and Oracle – Garr Godfrey Jan 27 '22 at 07:03
  • I have Laravel with MySql. Thank you. – Aleš Jan 27 '22 at 07:07
  • With MySQL, you can do this directly, if you don't need separate columns. See: `GROUP_CONCAT`. You would first aggregate per store/fruit to obtain the sums and then aggregate per store to apply `GROUP_CONCAT` to pull each fruit/sum per store into the same result row. There are other approaches, depending on how much you know about the number of fruit or whether you want to take a two step approach, where step 1 builds SQL based on the max number of fruit. Step 2 executes that SQL. – Jon Armstrong Jan 27 '22 at 07:14
  • Please run `SELECT version();` to check specific MySQL version. – FanoFN Jan 27 '22 at 07:40
  • Yes, it is. https://stackoverflow.com/questions/70874811/sql-dynamic-column-header-based-on-the-type – Ondřej Kolenatý Jan 27 '22 at 08:28

0 Answers0