Question 221507 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL SELECT . Rows to columns

I have a table with two fields.
The 1st is a ‘key’ and the 2nd field can have only value ‘A’ or ‘B’.
I would like, with a SELECT, to retrieve a recordset that, grouping by the 1st field, have two columns with the count of the values of the 2nd field.

Table:

Key Value
1 A
1 B
2 A
3 B

Result:

Key Counf of A Counf of B
1 1 1
2 1 0
3 0 1
Tags:

2

Answers


  1. 0 
    SELECT
    Key,
    SUM(CASE WHEN Value = 'A' THEN 1 ELSE 0 END) AS "Count of A",
    SUM(CASE WHEN Value = 'B' THEN 1 ELSE 0 END) AS "Count of B"
FROM
    {TABLE_NAME} // Replace 'TABLE_NAME' with the name of your table
GROUP BY
    Key;
    Login or Signup to reply.
  2. 0

    If you use MySQL (as it’s taged in your question), you don’t need CASE.

    The query will simply be:

    SELECT
  `Key`,
  SUM(`Value` = 'A') AS `Count of A`,
  SUM(`Value` = 'B') AS `Count of B`
FROM
  test
GROUP BY
  `Key`;

    If your RDBMS does not support above query, I would prefer COUNT here over SUM.

    COUNT returns zero if only NULL appears.

    Therefore the CASE doesn’t require an ELSE clause, so it’s more readable.

    SELECT
  `Key`,
  COUNT(CASE WHEN `Value` = 'A' THEN 1 END) AS `Count of A`,
  COUNT(CASE WHEN `Value` = 'B' THEN 1 END) AS `Count of B`
FROM
  test
GROUP BY
  `Key`;

    See this sample fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search