mysql query for a matrix

Question

I have some data from an evaluation. Possible values are (1,2,3,4,5) and are stored in a table, for each question 1 column.

Table evaluation:

f1 f2 q3 ...
1  5  2
2  4  3
.
.
.

I want to generate for 2 columns 1 matrix:

How often is f1=1 when q3=1?
How often is f1=1 when q3=2?
...
How often is f1=5 when q3=5?

So it will be in my case a 5*5 matrix. How can I solve this with mysql properly?

My first (working) attempt is to brute force it with 25 unions like:

SELECT count(0) FROM evaluation where q3 = 1 and f2 = 1
union all
SELECT count(0) FROM evaluation where q3 = 1 and f2 = 2
union all
...
union all
SELECT count(0) FROM evaluation where q3 = 5 and f2 = 5

But how can it be done in a nice way?

Marc B · Accepted Answer · 2012-11-17 04:03:58Z

1

SELECT f1, count(f1), f3
FROM evaluation
GROUP BY f3, f1

would give you the f1 counts for all values of f3.

answered Nov 17, 2012 at 4:03

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

solomyr Over a year ago

Thats's nearly the perfect query, wow. The only point is now, that i need to fill out the "no-matches" with zeros. Ill try to find a solution. Would it also be possible to get the result not in a list, rather in a table-like view?

Marc B Over a year ago

that kind of transformation is best done client-side. sql is more for fetching data. formatting/display isn't what the DB is for.

Collectives™ on Stack Overflow

mysql query for a matrix

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related