MySql using correct syntax for the over clause

Question

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;

Solution 1

There is no OVER clause in MySQL that I know of, but here is a link that might assist you to accomplish the same results:

http://explainextended.com/2009/03/10/analytic-functions-first_value-last_value-lead-lag/

Hope this helps.

Solution 2

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;     

Solution 3

MySQL does not currently support window functions, so over() will only yield syntax errors (or garbage, if it's accepted regardless).

Solution 4

MySQL Doesn't have window functions until the most recent release: MySQL 8 (release in April, 2018). MS SQL Server also accepts OVER clause.

The syntax is:

function(col1) OVER (PARTITION BY col2 ORDER BY col3)

Check out https://mysqlserverteam.com/mysql-8-0-2-introducing-window-functions/ for more examples.

View more solutions