CODEWITHSUNDEEP
mysqlsql
Jonathan Edgardo
Jonathan Edgardo

Reputation: 513

Group with independient columns in mysql query

I'm trying to group the results of a query and display in separate columns.

Table name: category_results

+---------+-----------------+
| cat_id  | Total           |
+---------+-----------------+
| CA001   |          150.00 | 
| CA002   |          130.00 | 
| CA002   |          200.00 |
| CA003   |           70.00 | 
| CA001   |           75.00 |
+---------+-----------------+

I want this result:

+--------+--------+--------+
| CA001  | CA002  | CA003  |
+--------+--------+--------+
| 255.00 | 330.00 | 70.00  | 
+--------+--------+--------+

Any ideas?

Upvotes: 0

Views: 32

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521804

You can use SUM combined with CASE WHEN to achieve what you want:

SELECT SUM(t.CA001) AS CA001,
       SUM(t.CA002) AS CA002,
       SUM(t.CA003) AS CA003
FROM
(
    SELECT CASE WHEN cat_id = 'CA001' THEN Total ELSE 0 END AS CA001,
           CASE WHEN cat_id = 'CA002' THEN Total ELSE 0 END AS CA002,
           CASE WHEN cat_id = 'CA003' THEN Total ELSE 0 END AS CA003
    FROM category_results
) t

Click the link below for a running demo.

SQLFiddle

Upvotes: 2

Related Questions