'SQL query how to find the average number of rental books for each quarter

I am trying to find the average number of rental books for each quarter.

Rental(RegNum, DateBorrowed, DateReturned)
Books(RegNum, BookName, Category)

What i am trying is

SELECT QUARTER(DateBorrowed) AS Quarter, COUNT(RegNo) As QuarterAvgRentalNum
FROM Rental
GROUP BY QUARTER(DateBorrowed);

However, I keep getting the Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column. Any ideas or suggestions?

mysqlsql


Solution 1:[1]

@StefanWuebbe I'm using mysql 8.0 – Amber

In MySql 8.0 the exact SQL statement you proposed seems to run successfully:

Create Table Rental (DateBorrowed Date, regNo Int);
  
SELECT QUARTER(DateBorrowed) AS Quarter, COUNT(RegNo) As QuarterAvgRentalNum
    FROM Rental
    GROUP BY QUARTER(DateBorrowed);

... tested in: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f96c6c1469bcb4c6ff5791cf814edf7a

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions