0

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 
+--------+------+-------+------------------+-------------+--------------------------+-----------+---------------+
jay sahu
  • 21
  • 2
  • 2
    Does this answer your question? [using of GROUP BY in mysql 8](https://stackoverflow.com/questions/54666896/using-of-group-by-in-mysql-8). Please, read the official documentation, use `GROUP BY` correctly. – Mike Doe Dec 09 '19 at 13:37

1 Answers1

0

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;

mascDriver
  • 35
  • 9