如何从mySQL表中分组/总数据？ [关闭]

Question

I have a table in the database that I want to perform a mySQL query on. Here's what the table looks like:

https://docs.google.com/spreadsheet/ccc?key=0At5_g34zSM41dFlNWUdNQnRPandiUUFuaUJzTEc1REE&usp=sharing

Given this table, I want to display the total number/count of users per journey and goalname.

I've tried to use GROUP BY and COUNT but what I only get is this:

As you can see, the data is only grouped by goalname. Is there a way where I could achieve this kind of result through mySQL query:? (see image below)

Something like this. I hope you could help me with this. Thanks in advance!

Answer 1

What you need is to PIVOT the columns into rows. Unfortunately, MySQL has no PIVOT table operator. But you can use the CASE expression to do this, like -

SELECT
  journey,
  SUM(CASE WHEN  goalname = 'Frank.net Hospital Cash Back' THEN count END) AS `Frank.net Hospital Cash Back`,
  SUM(CASE WHEN  goalname = 'Frank.net Life Cover' THEN count END) AS `Frank.net Life Cover`,
  SUM(CASE WHEN  goalname = 'Frank.net Salary Protection' THEN count END) AS `Frank.net Salary Protection`,
  SUM(CASE WHEN  goalname = 'King Price Car Insurance' THEN count END) AS `King Price Car Insurance`
FROM test
GROUP BY journey;

SQLFiddle example - http://sqlfiddle.com/#!2/314c6/8