CODEWITHSUNDEEP
phpmysqllocalhost
samar
samar

Reputation: 188

MYsql Error on my local machine.but it works fine on live

Here is my MySQL Query 

SELECT sum(quantity) as quantity_sold, `gc_order_items`.`name` as `name`, `sku` FROM `gc_orders` JOIN `gc_order_items` ON `gc_order_items`.`order_id` = `gc_orders`.`id` WHERE `status` != 'cart' AND `gc_order_items`.`type` = 'product' GROUP BY `product_id`

I am getting this error on local server.

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'go.gc_order_items.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by Please help!

Upvotes: 0

Views: 38

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521339

Here is your current query:

SELECT
    SUM(quantity) AS quantity_sold,
    gc_order_items.name AS name,
    sku
FROM gc_orders
INNER JOIN gc_order_items
    ON gc_order_items.order_id = gc_orders.id
WHERE status != 'cart' AND
      gc_order_items.type = 'product'
GROUP BY product_id

On your local machine most likely the ONLY_FULL_GROUP_BY mode is turned on. Under this mode, which agrees with the ANSI SQL standard, every column in a GROUP BY aggregation query must either appear in the GROUP BY clause or appear in an aggregate function, e.g. SUM(). In the case of your above query, you are selecting the columns sku and name, but they do not appear in GROUP BY, nor do they appear inside an aggregate function, hence the error.

On the live machine my guess is that strict mode is turned off, which allowed your query to proceed.

Here is a guess as to a version of your query which would run correctly everywhere:

SELECT
    t2.quantity_sold,
    t2.name,
    t1.sku
FROM gc_orders t1
INNER JOIN
(
    SELECT order_id, name, SUM(quantity) AS quantity_sold
    FROM gc_order_items
    WHERE type = 'product'
    GROUP BY order_id, name
) t2
    ON t1.id = t2.order_id
WHERE t1.status != 'cart'

Upvotes: 2

Related Questions