Group By on Column A and calculate value count on column B in SQL-CodePudding

I want to know about a query where I can perform a group by on Column A and can calculate the count of the values in column B and create a new table from it. Column A and B have limited types of values. (Categories)

Table:

A	B
a	X
b	Y
a	X
a	Z
b	Z
a	X

Result:

	X	Y	Z
a	3	0	1
b	0	1	1

CodePudding user response：

This is PIVOT type query. Converting row value to column.

-- MySQL
SELECT A ""
     , COUNT(CASE WHEN B = 'X' THEN 1 END) "X"
     , COUNT(CASE WHEN B = 'Y' THEN 1 END) "Y"
     , COUNT(CASE WHEN B = 'Z' THEN 1 END) "Z"
FROM test
GROUP BY A

Please check from url https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=33f2f4bcf423dccf7c79d6a8b2d64197

Use FILTER clause

-- PostgreSQL (v11)
SELECT A " "
     , COUNT(B) FILTER (WHERE B = 'X') "X"
     , COUNT(B) FILTER (WHERE B = 'Y') "Y"
     , COUNT(B) FILTER (WHERE B = 'Z') "Z"
FROM test
GROUP BY A

Please check from url https://dbfiddle.uk/?rdbms=postgres_11&fiddle=fec763cb0e5fed99b96e055dd587a235