CODEWITHSUNDEEP
mysqlsql
David Allen
David Allen

Reputation: 1163

Group by month and add multiple cols

Hi i have a table which stores feedbacks and Ratings (between 1 and 5)

I dont even know if this can be done with SQL or not.

Want I what to do is group by month and add up all Q1's, Q2's, Q3's and Q4 in to one column. The have the amount of records in that grouped month in another column.

Then I could use PHP to work out a average rate in those months.

  unixStamp      |  Q1   |   Q2   |   Q3    |   Q4
-----------------------------------------------------
  1362149504     |  5    |    4   |    3    |   5
  1362136774     |  5    |    5   |    2    |   5
  ..........     |  ...  |  ....  |  ....   |  ....

This is want I would like the achieve.

  Mnth           |  score  |   count  | 
---------------------------------------
  1              |  192    |    20    | 
  2              |  365    |    40    | 
  ..........     |  ...    |  ....    |

Upvotes: 1

Views: 81

Answers (2)

Taryn
Taryn

Reputation: 247690

You can use a CROSS JOIN to unpivot the data to get the result:

SELECT Mnth,
  sum(data) score,
  count(data) `count`
FROM 
(
  SELECT MONTH(FROM_UNIXTIME(unixStamp)) Mnth, 
    CASE q.col
      WHEN 'Q1' THEN Q1
      WHEN 'Q2' THEN Q2
      WHEN 'Q3' THEN Q3
      WHEN 'Q4' THEN Q4
    END AS DATA
  FROM yourtable t
  CROSS JOIN 
  (
    SELECT 'Q1' AS col
    UNION ALL SELECT 'Q2'
    UNION ALL SELECT 'Q3'
    UNION ALL SELECT 'Q4'
  ) q
) s
group by Mnth

See SQL Fiddle with Demo

Upvotes: 2

Michael Berkowski
Michael Berkowski

Reputation: 270617

The GROUP BY can contain an arbitrary expression (or the alias from a SELECT expression), and you can SUM() the 4 quarterly columns together as an aggregate.

SELECT
  /* Retrieve only the month from unixStamp */
  MONTH(FROM_UNIXTIME(unixStamp)) AS Mnth,
  /* SUM() as an aggregate the 4 quarterly cols */
  SUM(Q1+Q2+Q3+Q4) AS score,
  /* COUNT(*) aggregate will list number of records for group */
  COUNT(*) AS `count`
FROM yourtable
GROUP BY Mnth

Note that the above will group by the month only, not the month/year combination which would seem more appropriate. If you need that, use something like

/* Supplies month like 2013-02 */
SELECT
  DATE_FORMAT(FROM_UNIXTIME(unixtimestamp), '%Y-%m') AS Mnth,
  ...

Upvotes: 1

Related Questions