-1

My data table is as below:

ID WEEK   RESULT 
1   13     GOOD
2   13     BAD
3   13     GOOD
4   13     WORST
5   14     GOOD
6   14     BAD
7   14     WORST
8   15     BAD
9   15     WORST

I need a sql query to create an array as below:

WWEK   GOOD_RESULT   BAD_RESULT   WORST_RESULT   TOTAL
13            2             1       1              4
14            1             1       1              3
15            0             1       1              2

Can anyone please help me to find an appropriate mysql query?

Taryn
  • 242,637
  • 56
  • 362
  • 405
Saleej Kuruniyan
  • 1
  • 1
  • What do you mean by array? This question is very similar to your previous one -- http://stackoverflow.com/questions/16365894/mysql-query-to-select-a-distinct-column-and-the-count-of-a-value-in-another-colu If you need more columns, then you will add additional case/aggregates. Do you need this data returned to your application? Please clarify what you need. – Taryn May 03 '13 at 19:44
  • These links may be helpful: http://stackoverflow.com/questions/3480803/pivot-unpivot-tables-mysql http://stackoverflow.com/questions/7674786/mysql-pivot-table – Melanie May 03 '13 at 19:45

1 Answers1

0
SELECT
  WEEK,
  SUM(RESULT='GOOD') As GOOD_RESULT,
  SUM(RESULT='BAD') As BAD_RESULT,
  SUM(RESULT='WORST') AS WORST_RESULT,
  COUNT(*) As TOTAL
FROM YourTable
GROUP BY
  WEEK

Please see fiddle here.

fthiella
  • 48,073
  • 15
  • 90
  • 106