group query not supporting mysql 8;

Below query is working fine in old version of MySQL.

mysql> SELECT business_name, city,
    -> SUM(lot_count) as lot_count,
    -> SUM(lot_sold) as lot_sold FROM table_1 GROUP BY business_id, business_location_id;

OUTPUT

+--------+------+-------+------------------+-------------+-------
| business_name            | city      | lot_count | lot_sold  
----------+--------------------------+-----------+---------------+
| test_business_1          | Amsterdam |         4 |       100 
| test_business_2          | Macon     |         10|       30
+--------+------+-------+------------------+-------------+--------

this query is not working in MySQL 8;

OUTPUT with the above query in MySQL 8

ERROR 1055 (42000):
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'table_1.business_name' which is not functionally dependent on columns in GROUP BY clause;
this is incompatible with sql_mode=only_full_group_by

Below is the data in the table.

Select * from table_1;

+--------+------+-------+------------------+-------------+--------------------------+-----------+---------------+
| id     |  business_id | business_location_id | business_name            | city      | lot_count | lot_sold  
+--------+------+-------+------------------+-------------+--------------------------+-----------+---------------+
| 263862 |          151 |         176          | test_business_1            | Amsterdam |         1 |       25 
| 263859 |          151 |         176          | test_business_1            | Amsterdam |         1 |       25 
| 263860 |          151 |         176          | test_business_1            | Amsterdam |         1 |       25 
| 263861 |          151 |         176          | test_business_1            | Amsterdam |         1 |       25 
| 263863 |          181 |         221          | test_business_2            | Macon     |         2 |       10 
| 263864 |          181 |         221          | test_business_2            | Macon     |         2 |       10 
| 263866 |          181 |         221          | test_business_2            | Macon     |         6 |       10 
+--------+------+-------+------------------+-------------+--------------------------+-----------+---------------+

1 answer

  • answered 2019-12-09 13:35 mascDriver

    try this

    mysql> SELECT business_name, city, -> SUM(lot_count) as lot_count, -> SUM(lot_sold) as lot_sold FROM table_1 GROUP BY business_id, business_location_id,lot_count,lot_sold;