Subqueries without using the GROUP BY clause

Question

Here is my task:

List the full name of members, their DETAIL_DAILYLATEFEE and the average of DETAIL_DAILYLATEFEE for all members whose DETAIL_DAILYLATEFEE is less than the average of all DETAIL_DAILYLATEFEE.

Your query MUST contain a subquery. Do not use GROUP BY in your query.

Your result should be as shown below. Note that each member is only listed once.

Here is what I have so far:

SELECT DISTINCT(CONCAT(MEM_FNAME, " ", MEM_LNAME)) AS "Full Name",
detailrental.DETAIL_DAILYLATEFEE,
AVG(DETAIL_DAILYLATEFEE) AS "Average late fee" FROM membership
JOIN rental ON membership.MEM_NUM =  rental.MEM_NUM
JOIN detailrental ON rental.RENT_NUM = detailrental.RENT_NUM
WHERE detailrental.DETAIL_DAILYLATEFEE < 
    (SELECT AVG(DETAIL_DAILYLATEFEE) FROM detailrental);

I need to have the AVG(DETAIL_DAILYLATEFEE) column show up. But I'm getting the following error:

[ERROR 1140 (42000) at line 1: In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'mimir.membership.MEM_FNAME'; this is incompatible with sql_mode=only_full_group_by
query returned no results!][1]

[1]: https://i.stack.imgur.com/uIpzL.png # This account is new so I can't post pictures yet

I suspect I'm supposed to use CASE but I'm pretty new to it so I'm not sure how I'd go about doing that.

Please provide sample data, otherwise it's difficult for people to contribute answers that fulfill your requirements. You are also missing the expected result when you say `Your result should be as shown below.` — joseph, Mar 10 '21 at 21:47

Nelson · Answer 1 · 2021-03-10T20:41:37.690

0

SELECT DISTINCT
       (CONCAT(MEM_FNAME, " ", MEM_LNAME)) AS "Full Name", 
       detailrental.DETAIL_DAILYLATEFEE, 
       (SELECT AVG(DETAIL_DAILYLATEFEE) FROM MEMBERSHIP) AS "Average late fee"
FROM membership
     JOIN rental ON membership.MEM_NUM = rental.MEM_NUM
     JOIN detailrental ON rental.RENT_NUM = detailrental.RENT_NUM
WHERE detailrental.DETAIL_DAILYLATEFEE <
(
    SELECT AVG(DETAIL_DAILYLATEFEE) FROM detailrental
);

edited Mar 10 '21 at 20:41

answered Mar 10 '21 at 20:23

Nelson

179
4

I am wondering why this answer was not useful. And why the person who down voted my answer, wouldn't leave a comment for context. – Nelson Mar 10 '21 at 20:32
I tried your suggestion and got this error: "In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'mimir.membership.MEM_FNAME'; this is incompatible with sql_mode=only_full_group_by" query returned no results! Thanks for your input though – Matthew Murphy Mar 10 '21 at 21:20
Ahh, okay. Well, sorry about that buddy. Probably easier to figure out if there is way to test it. Thanks for the feedback Matthew. I appreciate it. – Nelson Mar 10 '21 at 21:40
Side note, what kind of sql are you writing here? Because I wrote an example pretty close to yours in Oracle SQL Developer, and a different example in SSMS and both ran fine. – Nelson Mar 10 '21 at 21:50
It's MySQL on a Mimir (university style software) platform – Matthew Murphy Mar 10 '21 at 21:53
Give this answer a read - https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql. I am not familiar with what you are running into, but this article did reference your error. Some of the answers also reference how to disable only_full_group_by. Good luck! – Nelson Mar 10 '21 at 22:06

Subqueries without using the GROUP BY clause

1 Answers1