CODEWITHSUNDEEP
mysqlsqldatabase
MJ13
MJ13

Reputation: 195

Can't run specific SQL query on my database

I have a database for a chat application. 

CREATE TABLE Users (uid int PRIMARY KEY, name text, phone text );

CREATE TABLE Messages (recipient int REFERENCES Users(uid), sender int 
REFERENCES Users(uid), time timestamp NOT NULL, message text NOT NULL, 
PRIMARY KEY (recipient, sender, time));

http://www.sqlfiddle.com/#!9/bd36d1

I want to define, for each of the 5 users which have sent the most messages, the average length of messages that have been sent by this user.

I have written the following query:

SELECT avg(strlen(message))
FROM Messages
WHERE sender IN
                (SELECT *
                 FROM (SELECT sender, COUNT(sender) AS NumberOfMessages
                       FROM Messages
                       GROUP BY sender) AS MessagesPerSender
                 ORDER BY NumberOfMessages DESC
                 LIMIT 5)

To start with, is this query correct? Does it give me the desired result? The problem is I can't run it at all cause I get the error:

"This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery"

Upvotes: 1

Views: 762

Answers (3)

Michele La Ferla
Michele La Ferla

Reputation: 6884

You had 2 errors in your code:

  1. first of all you cannot use strlen in MYSQL. That is an Microsoft SQL Server dialect Instead you need to use length.

  2. Secondly, in the subquery you used, you were using two columns instead on one. This will cause the query to fail because the equals operator needs to be equal to the value in only one column.

So here is your query:

select u.name, avg(length(m.message)), count(*)
from Messages m
inner join Users u on m.sender = u.uid
group by u.name
order by avg(length(m.message)) desc limit 5;

I improved on P. Salmon's answer since I provided you with the name of the sender rather than their ID.

Hope this helps :)

Upvotes: 2

P.Salmon
P.Salmon

Reputation: 17615

Not the right approach for mysql this may do

select sender,avg(length(message)),count(*)
from   messages
group by sender
order by avg(length(message)) desc limit 5;

+--------+----------------------+----------+
| sender | avg(length(message)) | count(*) |
+--------+----------------------+----------+
|      1 |               9.0000 |        1 |
|      9 |               5.5000 |        2 |
|      2 |               5.0000 |        1 |
+--------+----------------------+----------+
3 rows in set (0.00 sec)

Note this may not deal with draws in the way you want.

Upvotes: 2

TheWildHealer
TheWildHealer

Reputation: 1616

To find out, I have changed the DMBS from MySQL to Postgres, which supports inner limit. Your query has correct syntax, except the strlen() function, the correct one is length().

However, your query fails for a simple reason: you are doing a where sender in (subquery), although your subquery returns two fields. The in operator only works with single field queries. Moreover, your subquery is composed of two queries, which can be simplified to one. The following query works on Postgres 9.6, and should work on whatever version of MySQL with inner limit support:

SELECT avg(length(message))
FROM Messages
WHERE sender IN (
    SELECT sender
    FROM Messages
    GROUP BY sender
    ORDER BY COUNT(sender) DESC
    LIMIT 5
)

It produces the following result when run on your sample data:

+----------+
|   avg    |
+----------+
|   6.25   |
+----------+

Working SQL Fiddle (Postgres 9.6): http://www.sqlfiddle.com/#!17/bd36d/6/0

Upvotes: 1

Related Questions