1

I'm trying to create a view that groups score data for each week and displays it for each user but I don't know which commands to use.

Sample data:

// scorecards table
week | user | score    |
1    | 1    | 100      |
1    | 2    | 50       |
1    | 3    | 75       |
2    | 1    | 20       |
2    | 2    | 30       |
2    | 3    | 40       |

Desired output:

week | user1 | user2 | user3
1    | 100   | 50    | 75
2    | 20    | 30    | 40
  • If userlist is fixed list, you may use conditional grouping to pivot data. If not, you need to create stored procedure which uses prepared statement to obtain the data in the form you need. – Akina Jul 16 '18 at 4:47
0

MySQL pivot can be approximated through the use of aggregates, as in:

SELECT s.score_week
    , User1 = sum(s.user1)
    , User2 = sum(s.user2)
    , User3 = sum(s.user3)
FROM (
    SELECT s.score_week
        , CASE WHEN s.score_user = 1 THEN SUM(s.score) ELSE 0 END AS User1
        , CASE WHEN s.score_user = 2 THEN SUM(s.score) ELSE 0 END AS User2
        , CASE WHEN s.score_user = 3 THEN SUM(s.score) ELSE 0 END AS User3
    FROM scorecards s
    GROUP BY s.score_week
        , s.score_user
    ) s
GROUP BY s.score_week;

See the fiddle here for more details.

The output looks like:

╔════════════╦═══════╦═══════╦═══════╗
║ score_week ║ User1 ║ User2 ║ User3 ║
╠════════════╬═══════╬═══════╬═══════╣
║          1 ║   100 ║    50 ║    75 ║
║          2 ║    20 ║    30 ║    40 ║
╚════════════╩═══════╩═══════╩═══════╝

The above example is hard-coded, and assumes the list of users doesn't change. If the list of users is dynamic, and more users get added weekly, you'll need to use dynamic SQL to build the query to include columns for each user at run-time.

0

Since MySQL doesn't allow FULL OUTER JOIN this is a bit trickier, but doable taking the week as main key for the left joins.

select distinct s.week, user1, user2, user3 from scorecards s left outer join 
(select week, score as user1 from scorecards where user = 1) a
on s.week = a.week left join
(select week, score as user2 from scorecards where user = 2) b
on s.week = b.week left join
(select week, score as user3 from scorecards where user = 3) c
on s.week = c.week
;
  • MySQL has OUTER joins (LEFT and RIGHT), like the ones you used! It only lacks FULL outer joins. (I guess you meant that it doesn't have FULL OUTER JOIN.) – ypercubeᵀᴹ Jul 16 '18 at 8:02
  • Good point, I meant full outer join, which is what I would use in this case in other DBMS. I'll edit the answer, thanks for point it out. – LironCareto Jul 16 '18 at 8:55
  • In MySQL it's enough to use something like SELECT `week`, MAX(CASE WHEN `user`=1 THEN score END) user1, ... FROM scorecards GROUP BY `week` . – Akina Jul 16 '18 at 9:50
  • I think your solution is only partially right, @Akina because if you have more than one score per user and week you would be missing it. And I didn't know if that was the case. – LironCareto Jul 16 '18 at 11:25
  • if you have more than one score per user and week you would be missing it The task has no sense if (week,user) is no UNIQUE - otherwise the author would say what to do in this case (sum, concat, min/max, etc.). Your solution will give strange output (multiple rows per user and week pair) in that case too... – Akina Jul 16 '18 at 11:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.