On gists

Pivot table (2 zpusoby)

MySql MySql - advanced AW

pivot-table.sql Raw #

-- https://diskuse.jakpsatweb.cz/?action=vthread&forum=28&topic=174158
-- Pivotni tabulka

-- Ja
SELECT user_id, (
  SUM(IF (action_type = 'login', 1, 0)) 
) AS 'login', (
  SUM(IF (action_type = 'publish', 1, 0)) 
) AS 'publish', (
  SUM(IF (action_type = 'download', 1, 0)) 
) AS 'download', (
  SUM(IF (action_type = 'upload', 1, 0)) 
) AS 'upload', (
  SUM(IF (action_type = 'comment', 1, 0)) 
) AS 'comment',(
  SUM(IF (action_type = 'share', 1, 0)) 
) AS 'share'
 
FROM ss_stats
GROUP BY user_id
HAVING user_id IS NOT NULL


-- Kajman
SELECT user_id,
       ( Sum(IF (action_type = 'login', pocet, 0)) )    AS 'login',
       ( Sum(IF (action_type = 'publish', pocet, 0)) )  AS 'publish',
       ( Sum(IF (action_type = 'download', pocet, 0)) ) AS 'download',
       ( Sum(IF (action_type = 'upload', pocet, 0)) )   AS 'upload',
       ( Sum(IF (action_type = 'comment', pocet, 0)) )  AS 'comment',
       ( Sum(IF (action_type = 'share', pocet, 0)) )    AS 'share'
FROM   (SELECT user_id,
               action_type,
               Count(*) pocet
        FROM   ss_stats
        GROUP  BY action_type,
                  user_id) pocty
WHERE  user_id IS NOT NULL
GROUP  BY user_id


--- tttt; nejlepsi a nejrychlejsi
SELECT
    user_id,
    logins.count AS 'login', 
    publishes.count AS 'publish', 
    downloads.count AS 'download', 
    uploads.count AS 'upload', 
    "comments".count AS 'comment', 
    shares.count AS 'shares'
FROM 
  users 
  LEFT JOIN (SELECT user_id, COUNT(*) FROM ss_stats WHERE action_type = 'login' GROUP BY user_id) logins USING(user_id)
  LEFT JOIN (SELECT user_id, COUNT(*) FROM ss_stats WHERE action_type = 'publish' GROUP BY user_id) publishes USING(user_id)
  LEFT JOIN (SELECT user_id, COUNT(*) FROM ss_stats WHERE action_type = 'download' GROUP BY user_id) downloads USING(user_id)
  LEFT JOIN (SELECT user_id, COUNT(*) FROM ss_stats WHERE action_type = 'upload' GROUP BY user_id) uploads USING(user_id)
  LEFT JOIN (SELECT user_id, COUNT(*) FROM ss_stats WHERE action_type = 'comment' GROUP BY user_id) "comments" USING(user_id)
  LEFT JOIN (SELECT user_id, COUNT(*) FROM ss_stats WHERE action_type = 'share' GROUP BY user_id) shares USING(user_id)