Vasilis Papag Vasilis Papag - 1 month ago 7
MySQL Question

My sql results from rows to column

is there a way to insert some values of the COUNT(*) into these columns. To explain. I have a table

+--------+----------+
| url | type |
+--------+----------+
| URl1 | A |
+--------+----------+
| URL1 | B |
+--------+----------+
| URL2 | C |
+--------+----------+
| URL2 | C |
+--------+----------+


where the values is how many times the type (A,B,C) appears in the data set (this is why i want to COUNT)
i want to have something like this at my final table

+--------+------+-----+-----+
| url | A | B | C |
+--------+------+-----+-----+
| URL1 | 3 | 1 | 0 |
+--------+------+-----+-----+
| URL2 | 2 | 0 | 1 |
+--------+------+-----+-----+


Until now i did this

SELECT
url, type, COUNT(*)
FROM
table
GROUP BY url,type


and i'm getting this (random example)

|url |type |COUNT of a specific type for this url|
https://1 A 1
https://2 A 1
https://2 B 1
https://3 B 1
https://4 C 4
https://4 B 2
https://4 B 2


and i want to insert the values like the table above

Answer

You could use case when and group by

  select  url,
          sum(case when type ='A' then 1 else 0 end) as A,
          sum(case when type ='B' then 1 else 0 end) as B,
          sum(case when type ='C' then 1 else 0 end) as C      
  FROM my_table
  group by url ;