MySQL - Average by columns in group by clause-CodePudding

I am trying to do aggregations based on columns specified in MySQL query. Here's some sample data:

CREATE TABLE MyTable (id char(10), name char(10), price int, cat int, size int);

INSERT INTO MyTable (id, name, price, cat, size) 
VALUES ('2065511307', 'abc', 2449, 1, 600), 
       ('65fH72', 'bcd', 4395, 1, 851), 
       ('65fH72', 'bcd', 4995, 2, 985),
       ('65fH72', 'bcd', 4678, 1, 865);

I'd like to group by id and cat columns and compute the avg of price and size columns. When I run the query, I don't get the results I expect.

    SELECT id, cat, AVG(price) as avg_price, AVG(size) as avg_size
    FROM table1
    GROUP BY id, cat
    HAVING avg_price <= 10000

The results I expect to see:

CodePudding user response：

Here is a query that will get you the correct result.

SELECT id, name, ROUND(AVG(price), 0) AS avg_price, cat, ROUND(AVG(size), 0) AS avg_size
FROM MyTable
GROUP BY id, cat
HAVING avg_price <= 10000;

Note, you may have to turn the ONLY_FULL_GROUP_BY option off to get this query to run. This also assumes name is functionally dependent on id.

The query below will work if you are unable to turn the ONLY_FULL_GROUP_BY option off.

SELECT id, 
  (SELECT name FROM MyTable AS mt WHERE mt.id=MyTable.id LIMIT 1) AS name,
  ROUND(AVG(price), 0) AS avg_price, cat, ROUND(AVG(size), 0) AS avg_size
FROM MyTable
GROUP BY id, cat
HAVING avg_price <= 10000;

CodePudding user response：

I do not understand your expected output, but this is This MySQL ORDER BY would return all records sorted by the id order, with a secondary sort by cat order. Therefore, it is linked to two remaining, and it is not one values