Getting grouped data in column format rather than by rows from a MySQL query

Question

It's difficult to make a more descriptive title for this. I've spent a couple of hours on it now and figured best to just ask someone!

I have 3 tables, which look something like this

Locations:

    id    |    name    
    1     |   Plymouth
    2     |   Torquay
                   etc

salesRecords

    itemId   |    saleDate (unix)    |    qty    |    saleLocation    |
      123    |    UNIXTIMESTAMP      |     1     |          1         |
      458    |    UNIXTIMESTAMP      |     4     |          2         |
      215    |    UNIXTIMESTAMP      |     2     |          2         |
      541    |    UNIXTIMESTAMP      |     1     |          1         |

products

    itemId   |    brand    |    
      123    |    1        |     etc

Obviously each table actually contains more data than that, but that's all that is required for this.

What I need is an output which can tell me how many of each item, or group of items a particular location has sold. I need something that looks like this:

OUTPUT

    mnth    |    yr    |    location1Sales    |     location2Sales  
     10     |   2006   |          14          |           7
     11     |   2006   |          13          |           12
     12     |   2006   |          8           |           1      ... etc

The question that I posted here yesterday:

MySQL subquery to get a list of IDs from one table to query a second table

Was kindly answered and gave me a fairly efficient query to retrieve a TOTAL of combined sales across all locations (there's no location data related to the query posted in yesterday's question at all) and that works fine.

I can of course add an additional clause to the WHERE and get a single location's data only, but I want all of them in one table.

Today am trying to add in some grouping/where clause to add the locations into the query but can't get my head around how to create a table of the format above.

I've got as far as getting location data output in rows, but I need it in column form as per the above

SELECT DAY( FROM_UNIXTIME( saleDate ) ) AS 
   DAY , MONTH( FROM_UNIXTIME( saleDate ) ) AS mnth, 
   YEAR( FROM_UNIXTIME( saleDate ) ) AS yr, 
   COUNT( s.id ) AS invCount, locations.name, 
   s.location, SUM( quantity ) AS saleQty 
FROM salesRecords s
     LEFT JOIN locations ON locations.id = s.location
     INNER JOIN products ON s.itemNo = products.id
     WHERE products.brand =  '313' <=== X
 AND s.location LIKE  '1\_%' <=== XX
 GROUP BY  `locations`.`name` , mnth, yr

X: brand ID is a variable required to group products by brand

XX: locations are queried like this as the saleLocation is held in the dB in the form "locationId_tilId" as sales are tracked by til, although for the purpose of this data, I need all by location ID only, I'm not concerned with the tilId

I am trying to get this data and turn it into a Javascript array so I can use the brilliant amCharts plugin to chart it.

It looks like you're trying to do a crosstab or pivot operation. — Matt Fenwick, May 04 '12 at 13:11
Yes, I guess it would be a kind of pivot operation. I have no idea if this is even possible with MySql? — Jamie Hartnoll, May 04 '12 at 13:17
If you know ahead of time the number and name of the columns in the result, it's not too bad. .... otherwise it's quite ugly, typically using stored procedures to generate dynamic SQL — Matt Fenwick, May 04 '12 at 13:23
from the title i read above i thought you need data in a single column. Is it correct then i have a solution for that — Muhammad Raheel, May 04 '12 at 13:35
`SQL` is not a Turing-complete language. Some stuff are better done in application code. Pivoting is one such case (unless your use case is really, really narrow) as you cannot make an SQL query that does it with an arbitrary number of columns (locations in this case). — Romain, May 04 '12 at 13:51
Hmmm... could I do it in two stages? Get the number of locations and the location names with one query and then use that somehow? — Jamie Hartnoll, May 04 '12 at 13:52

score 0 · Answer 1 · answered Feb 08 '13 at 01:54

select
      year( FROM_UNIXTIME( saleDate )) as yr,
      month( FROM_UNIXTIME( saleDate )) as mnth,
      sum( if( saleLocation = 1, qty, 0 )) as Location1Sales,
      sum( if( saleLocation = 2, qty, 0 )) as Location1Sales
   from
      salesRecords
   group by
      year( FROM_UNIXTIME( saleDate )),
      month( FROM_UNIXTIME( saleDate ))

If you have more sales locations, just add them to the SUM( IF() ) constructs...

Getting grouped data in column format rather than by rows from a MySQL query

1 Answers1