MySql using correct syntax for the over clause

mysql sql window-functions

Nightwolf · Jun 9, 2011 · Viewed 42.6k times · Source

What is the correct syntax to get the over clause to work in mysql?

I would like to see the total sms's sent by each user without grouping it with the group by clause.

SELECT 
    username, 
    count(sentSmsId) OVER (userId) 
FROM 
    sentSmsTable,
    userTable
WHERE
    userId = sentUserId;

Answer

MySQL 8 has got the window functions! Therefore, you can write your query in it like this:

SELECT username, 
       count(sentSmsId) OVER (partition by userId) 
FROM sentSmsTable
JOIN userTable ON userId = sentUserId;