Group by date and store in SQL

Question

I'm having some trouble doing a request in SQL.

My table looks like this

id | storeID | dateDone | paidSum

I need make a request that enables me to make a html table like this

date  | store One | store Two | store Tree
08/12 | 2000      | 6000      | 1000    
08/13 | 100       | 90        | 10000

So the array should look like this

[
"08/12" => [
  "total" => 9999,
  "byPlace" => [
    "1" => 800,
    "2" => 777,
     .....
   ]
 ],
....
]

Can this be done in one SQL request, or do I have to continue using foreach loops (which takes a looong time to load)?

The database contains +10k entries. I'm using MariaDB

Then it just groups by date, only one storeId is taken. I need the detail by store and by date. — BearInBox, Aug 23 '17 at 13:50
Possible duplicate of [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) — Juan Carlos Oropeza, Aug 23 '17 at 14:02
@juancarlosoropeza I wouldn't use a mysql pivot query for this. — Strawberry, Aug 23 '17 at 14:35

score 0 · Answer 1 · answered Aug 23 '17 at 13:48

Two things here.

Grouping by date and store is straightforward.

SELECT SUM(paidSum) total,
       DATE(dateDone) dateDone,
       StoreId
  FROM table
 GROUP BY DATE(dateDone), StoreId
 ORDER BY 1, 2

This gives you a result set with a row for each date and store. It will yield everything you need in a single query, getting rid of your subqueries.

Displaying the stores as columns involves pivoting the resultset. Pivoting in MySQL is a notorious pain in the neck, and is generally easier to do in a php or other application program.

But then you have duplicates of dates. Which can be resolved with loops, but it takes 3-4 secs to process, a bit too long. — BearInBox, Aug 23 '17 at 13:56

Group by date and store in SQL

1 Answers1