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"

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.

Can, Can was the leading avant-garde rock group of the 70s. Can experimented with noise, synthesizers, non-traditional music, cut-and-paste techniques, and, most� Can definition, to be able to; have the ability, power, or skill to: She can solve the problem easily, I'm sure. See more.

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 :)

The Official CAN / Spoon Records Website, Can may refer to: Contents. 1 Containers; 2 Music; 3 Other; 4 See also. Containers[edit]. Aluminum can � Drink can � Oil can � Steel and tin cans � Trash can� Define can. can synonyms, can pronunciation, can translation, English dictionary definition of can. to be able to, have the power or skill to: I can take a bus to the

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

Can, 1.4Used to indicate that something is typically the case. 'antique clocks can seem out of place in modern homes'. More example sentences. 110 synonyms of can from the Merriam-Webster Thesaurus, plus 97 related words, definitions, and antonyms. Find another word for can. Can: to bring (as an action or operation) to an immediate end.

Can (band), can noun [C] (CONTAINER) � can verb [T] (CONTAIN) � can modal verb (ABLE) � can modal verb (PERMIT) � can modal verb (BE POSSIBLE) � can� Can (stylised as CAN) was a German experimental rock band formed in Cologne in 1968 by the core quartet of Holger Czukay (bass, tape editing), Irmin Schmidt (keyboards), Michael Karoli (guitar), and Jaki Liebezeit (drums).

Can, VerbEdit. can (third-person singular simple present can, present participle -, simple past could, past participle (obsolete except in adjectival use) couth). HANGZHOU, China, Aug. 04, 2020 (GLOBE NEWSWIRE) -- Canaan Inc. (NASDAQ: CAN) ("Canaan" or the "Company"), a leading high-performance computing solutions provider, today announced that the terms of

CAN, Word forms � 1. countable noun A can is a metal container in which something such as food, drink, or paint is put. � 2. verb [usually passive] When food or drink is� A Controller Area Network (CAN bus) is a robust vehicle bus standard designed to allow microcontrollers and devices to communicate with each other's applications without a host computer. It is a message-based protocol , designed originally for multiplex electrical wiring within automobiles to save on copper, but can also be used in many other

Comments
  • What version of MySQL are you using?
  • @Someguywhocodes MySQL 5.6 .. It's on the link
  • See stackoverflow.com/questions/17892762/…
  • @RonaldHaan Is the query theoritically correct? Does it gives the result I want?
  • If you can't run the query, does it really matter if it would return the correct result on a supported version?
  • Thanks for the answer!I would like to ask something more if u are willing to help. If I wanted to define all the users who send messages and have a phone starting with "83" why does the following quote returns me 0? Do I use LIKE wrong? sqlfiddle.com/#!17/7303a5/1
  • When using LIKE, you need to use the percentage sign for it to work well. Your query would be something like this: select u.name, count(m.sender) from Users u inner join Messages m on m.sender = u.uid where u.phone LIKE '83%' group by u.name having count(m.sender) > 1; I included the group by and having clauses since you asked for those users who at least sent one message.
  • One last question that confused me. Let's take all the users that have received (one or many) messages from the user 'peter'. I want to define the number of users that have sent (one or many) messages to ALL the users that have received a message from 'peter' . On my query I define the number of users that have sent messages to at least one of the users that have received a message from 'peter'. How can I modify it to make it give me the desired result? Thanks in advance! sqlfiddle.com/#!17/a63bc/2
  • This was tricky :) The query won't return any results because in reality no user who has received a message from Peter, has in turn messaged ALL the other users who received a message from Peter. However if you need the query, here it is: sqlfiddle.com/#!9/a63bc7/1/0
  • Thanks a lot!I am just not used in the newer explicit join style. I tend to use the join syntax with the where clause. So I'll have to take my time to check it. Seems to work fine by the way!
  • Wasn't so sure either, but turns out you can, both on Postgres and MySQL. Please mark my answer as accepted if it answers the question.
  • The answer is helpful. The only problem is that I think it doesn't return the desired result cause what I want is the average length of messages for the top 5 users (5 results). This returns the average length of messages from the average length of messages for each one of the top 5 users (1 result).
  • Okay, did not understand it like that. Then P.Salmon answer's is the one you are looking for. I have suggested an edit to your question to better reflect what you want.
  • Yeah the query is indeed ambiguous. I am not even 100% sure if this is exactly what the query asks. Just let the answer as it is cause it helped me understand better the problem and there's a possibility it answers my question as well. Thanks for helping me.
  • I would like to ask something more if u are willing to answer. If I wanted to define all the users who send messages and have a phone starting with "83" why does this quote returns me 0? Do I use LIKE wrong? sqlfiddle.com/#!17/7303a5/1