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

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!

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

Below query might work in your case.

SELECT 
a.journey, 
a.goalname, 
FIND_IN_SET(a.goalname, (SELECT GROUP_CONCAT(goalname) FROM <table_name> b.journey =  a.journey)) AS no_of_goalname
FROM <table_name> a

you've already accepted an answer, but just for fun, if you wanted to dynamically generate the column names, so that if new entries of goalName are entered you don't have to change your code you can use the below (sqlFiddle)

DROP PROCEDURE IF EXISTS  getCount//
CREATE PROCEDURE getCount ()
BEGIN
  -- First we declare all the variables we will need
  DECLARE loopGoalName VARCHAR(100);
  DECLARE dynamicSql VARCHAR(5000);
  DECLARE finalSql VARCHAR(5000);
  -- flag which will be set to true, when cursor reaches end of table
  DECLARE exit_loop BOOLEAN;         

  -- Declare the sql for the cursor
  DECLARE example_cursor CURSOR FOR
    SELECT DISTINCT goalName
    FROM YourTableName;

  -- Let mysql set exit_loop to true, if there are no more rows to iterate
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;
  SET dynamicSql = '';
  SET finalSql = '';
  -- open the cursor
  OPEN example_cursor;

  -- marks the beginning of the loop
  example_loop: LOOP

    -- read the name from next row into the variable l_name
    FETCH  example_cursor INTO loopGoalName;
    -- check if the exit_loop flag has been set by mysql, 
    -- if it has been set we close the cursor and exit 
    -- the loop
    IF exit_loop THEN
        CLOSE example_cursor;
        LEAVE example_loop;
    END IF;
    SET DynamicSql = CONCAT(DynamicSql,",SUM(IF(goalName='",loopGoalName,"',1,0)) as `",loopGoalName,"`");
  END LOOP example_loop;
  SET finalSql = CONCAT('SELECT journey',DynamicSql,
                         ' FROM yourTableName
                           GROUP BY journey');
  -- now we run set some variables and run the dynamically built query
  SET @finalSql = finalSql;
  PREPARE stmt1 FROM @finalSql;
  EXECUTE stmt1;
END//