0

Say I have the following table:

| type        | date         | count       |
+-------------+--------------+-------------+
| cat         | 2014-2-5     |      2      |
| horse       | 2014-2-7     |      3      |
| cat         | 2014-1-28    |      1      |
| dog         | 2014-7-12    |      5      |
| cat         | 2014-9-30    |      7      |
+-------------+--------------+-------------+

I want to see how much of each type was sold each month i.e

| type  | Year      | 2014Month=1 | 2014Month=2 |2014Month=7 |2014Month=9 |
+-------+-----------+-------------+-------------+------------+------------+
| cat   | 2014      |      1      |      2      |      0     |      7     |
| horse | 2014      |      0      |      3      |      0     |      0     |
| dog   | 2014      |      0      |      0      |      5     |      0     |
+-------+-----------+-------------+-------------+------------+------------+

So far the closest thing I could come up with is to do it one month at a time e.g. for February 2014:

SELECT   type, YEAR(date), SUM(count)
FROM     myTable
GROUP BY type
WHERE    date BETWEEN "2014-2-1"
                  AND "2014-2-31";

And then manually rerun changing the dates for each month. There must be an easier way.

2
  • Which RDBMS are you using? MSSQL added a PIVOT operator in the 2005 version. Feb 5 '15 at 14:49
  • As per tag mysql
    – meccooll
    Feb 6 '15 at 22:41
0

OK, you forced me into it. I have created a Stored Procedure that will generate and execute the ugly SELECT statement to Pivot your data.

http://mysql.rjweb.org/doc.php/pivot

Assuming your date is a DATE type (although it does not look like it!), my SP would be called something like

CALL Pivot('myTable',             -- Your table
           'type, LEFT(date, 4)', -- the fields to be on the left
           'LEFT(date, 7),        -- the 'pivot' column (not formated quite like you wanted)
           'count',               -- the column to SUM up for the cells
           '',                    -- optional 'WHERE ...'
           ''                     -- optional 'ORDER BY ...'
);
0

Have you tried this one?

Case # 1

This SQL Query only check out in February month of 2014 of all type (that is Cat,dog,horse):

SELECT   type, YEAR(date), SUM(count)
FROM     myTable 
WHERE    date BETWEEN '2014-02-01'
                  AND '2014-02-28' 
GROUP BY type 
ORDER BY type;

Case # 2

This SQL Query check out the only type cat for the month of February 2014:

SELECT   type, YEAR(date), SUM(count)
FROM     myTable 
WHERE    date BETWEEN '2014-02-01'
                  AND '2014-02-28'
AND      type LIKE '%cat%' 
GROUP BY type 
ORDER BY type;

Case 3#

This SQL Query check out all type in the year 2014:

SELECT   type, YEAR(date), SUM(count)
FROM     myTable
WHERE    date BETWEEN '2014-01-01'
                  AND '2014-12-31'
GROUP BY type 
ORDER BY type;
2
  • 1
    To deal with month length, do something like WHERE date >= '2014-02-01' AND date < '2014-02-01' + INTERVAL 1 MONTH
    – Rick James
    Feb 6 '15 at 6:22
  • Ok so there's no easy way to get everything in one output and it has to be done separately per each interval (either all types per each month, or each type for every month)
    – meccooll
    Feb 6 '15 at 22:43
-1

Sorry, there is no simple answer. Use MySQL for the raw data; do pivoting in your UI.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.