My sql results from rows to column

Question

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

score 0 · Accepted Answer · answered Nov 05 '16 at 21:19

0

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 ;

answered Nov 05 '16 at 21:19

ScaisEdge

131,976
10
91
107

Thans for the fast answer it worked. I was trying something like this but with COUNT – Vasilis Papag Nov 05 '16 at 21:31
thanks .. in these case the use of case .. when .. is very useful .. – ScaisEdge Nov 05 '16 at 21:32

My sql results from rows to column

1 Answers1