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;

I would like to see the total sms's sent by each user without grouping it with the group by clause. — Nightwolf, Jun 09 '11 at 12:25

score 20 · Answer 1 · answered Nov 09 '17 at 10:37

20

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;

answered Nov 09 '17 at 10:37

Radim Bača

score 13 · Accepted Answer · answered Jun 09 '11 at 12:21

13

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:

Hope this helps.

answered Jun 09 '11 at 12:21

Chris Halgryn

13

This is no longer true for MySQL 8: https://dev.mysql.com/doc/refman/8.0/en/window-functions.html – Lukas Eder Sep 18 '18 at 20:08

score 5 · Answer 3 · answered Jun 09 '11 at 12:21

5

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

answered Jun 09 '11 at 12:21

Denis de Bernardy

gogolaygo · Answer 4 · 2018-05-30T16:23:39.873

3

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)

edited May 30 '18 at 16:23

answered May 30 '18 at 16:18

gogolaygo

4 Answers4