count different value in column, put into new columns [MySQL]

Question

I've been looking for a tutorial everywhere but I couldn't find any specific solution to this. Does anybody know how to query from this table:

//////////////////
places  //  type /
//////////////////
alabama //  ZX  //
alabama //  AQ  //
africa  //  AQ  //
vietnam //  FD  //
vietnam //  FD  //
////////////////

into this

///////////////////////////////////////////////////
places |      ZX       |     AQ      |     FD
///////////////////////////////////////////////////
alabama|      1        |      2      |     0
africa |      0        |      1      |     0
vietnam|      0        |      0      |     2
///////////////////////////////////////////////////

EDIT: I edited the format, sorry for the error earlier.

Welcome, what you are doing is called a pivot query. Best implementation depends on database engine, what are you using (MySQL, PostgreSQL, Oracle, SQL Server)? — Zerodf, Nov 07 '19 at 16:58
@Zerodf Thanks for reaching back to me. In this case, I am using MySQL. — hunjump23, Nov 07 '19 at 16:59

score 0 · Answer 1 · answered Nov 08 '19 at 03:20

Here is a possible solution that works across a variety of database engines.

-- Start: Setup Data
CREATE TABLE things (
  places VARCHAR(50), 
  type VARCHAR(50)
);  

INSERT INTO things VALUES ('alabama','ZX');
INSERT INTO things VALUES ('alabama','AQ');
INSERT INTO things VALUES ('africa','AQ');
INSERT INTO things VALUES ('vietnam','FD');
INSERT INTO things VALUES ('vietnam','FD');
-- End:  Setup Data

SELECT
  things.places,
  SUM(
    CASE 
      WHEN things.type = 'ZX' THEN 1
      ELSE 0
    END 
  ) AS ZX_COUNT,
  SUM(
    CASE 
      WHEN things.type = 'AQ' THEN 1
      ELSE 0
    END 
  ) AS AQ_COUNT,
  SUM(
    CASE 
      WHEN things.type = 'FD' THEN 1
      ELSE 0
    END 
  ) AS FD_COUNT
FROM things
GROUP BY things.places

The drawback to this solution is that you need to know a priori which columns you want to have in your pivot table.

Here is an very well thought out answer to your question.

count different value in column, put into new columns [MySQL]

1 Answers1