Sum column with condition and display in row

Question

I would like to sum a column with multiple condition and display all the result in rows

I have a table as

Status        Amount
pending         100
Success         50
pending         20
failure         80
success         20

Now I would like to find the total of pending, success and failure as

Pending        Success            failure
120             70                 80

something like

select sum(pending), sum(success), sum(failure) from mytable

Answer 1

Try this query:

SELECT
   SUM(CASE WHEN Status = 'pending' THEN Amount ELSE 0 END) AS Pending
  ,SUM(CASE WHEN Status = 'Success' THEN Amount ELSE 0 END) AS Success
  ,SUM(CASE WHEN Status = 'Failure' THEN Amount ELSE 0 END) AS Failure
FROM MyTable

You can also use this dynamic query:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN `Status` = ''',
      `Status`,
      ''' THEN Amount ELSE 0 END) AS `',
      `Status`, '`'
    )
  ) INTO @sql
FROM MyTable;

SET @sql = CONCAT('SELECT ', @sql,'
                     FROM MyTable
                  ');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See this SQLFiddle