MySQL Throws error after defining the location of table-CodePudding

I have this 'information' table below with 4 columns in it: 'creator_id','viewer_id','date_format','donation'

CREATE TABLE information (
    creator_id INT NOT NULL,
    viewer_id INT NOT NULL,
    date_format DATE NOT NULL,
    donation INT NOT NULL
);

INSERT 
    INTO twitch.information(creator_id,viewer_id,date_format,donation) 
    VALUES
    (10,11,'2014-01-02',34),
    (20,14,'2014-01-02',150),
    (30,15,'2014-01-02',717),
    (31,17,'2014-01-02',177),
    (32,17,'2014-01-06',737),
    (33,16,'2014-01-07',37),
    (40,18,'2016-03-08',442),
    (41,19,'2016-03-09',142),
    (42,10,'2016-03-10',152),
    (43,11,'2016-03-11',512),
    (44,12,'2016-01-12',340),
    (60,0,'2012-01-02',1000),
    (70,1,'2012-01-02',100);

SELECT creator_id,
    MAX(SUM(donation)/COUNT(donation)) AS "TOP AVG DONATION CREATOR ON YEAR 2014 (January)"
    WHERE date_format = "2014-01-02"
    FROM twitch.information;

I was approaching for to find the creator_id that have the highest average of donation on date of '2014-01-02' but my output console throws this error: Error code 1064: you have error in your SQL Syntax; check the manual corresponding...

I believe there's something wrong with my syntax but I couldn't tell what is it.

CodePudding user response：

Beside the problem the where clause must appear after the from, you don't need to do a division (which is risky because of possible divide by zero exceptions). You can use AVG, so "the creator_id that have the highest average of donation on date of '2014-01-02'" can be found that way:

SELECT creator_id,
AVG(donation) AS averageDonation
FROM information
WHERE date_format = "2014-01-02"
GROUP BY creator_id
ORDER BY 2 DESC LIMIT 1;

...or if you want it more clear:

SELECT creator_id,
AVG(donation) AS averageDonation
FROM information
WHERE date_format = "2014-01-02"
GROUP BY creator_id
ORDER BY averageDonation DESC LIMIT 1;

CodePudding user response：

Don't forget to group your data when using aggregation functions :

SELECT 
     creator_id
     , MAX(SUM(donation) / COUNT(donation)) AS "TOP AVG DONATION CREATOR ON YEAR 2014 (January)"
FROM twitch.information
WHERE date_format = "2014-01-02"
GROUP BY creator_id;

Update :

Use AVG aggregation function which handles division by zero potential error :

SELECT 
     creator_id
     , MAX(AVG(donation)) AS "TOP AVG DONATION CREATOR ON YEAR 2014 (January)"
FROM twitch.information
WHERE date_format = "2014-01-02"
GROUP BY creator_id;