Shop Dashboard Widget Error

Update:

Found the issue, which was related to the sqlmode. As of MySQL 5.7 ONLY_FULL_GROUP_BY is set by default, which causes an issue with the query and so returns an error.

I've run into an issue with the perch shop dashboard. In particular the 'Revenue Widget, which is displaying the following error

Warning
: count(): Parameter must be an array or an object that implements Countable in
/home/forge/newby.rootstaging.uk/public/admin/addons/apps/perch_shop/modes/shop.post.php
on line
208

Warning
: count(): Parameter must be an array or an object that implements Countable in
/home/forge/newby.rootstaging.uk/public/admin/addons/apps/perch_shop/modes/shop.post.php
on line
209

I've narrowed this down to an SQL query returning an error, the query is as follows

SELECT DATE_FORMAT(o.orderCreated, "%Y-%m-01") AS orderMonth, SUM(o.orderTotal / o.orderExchangeRate) AS revenue FROM perch3_shop_orders o WHERE o.orderStatus IN ('paid', 'processing', 'cancelled', 'dispatched', 'returned', 'partial_refund', 'refunded') AND o.orderDeleted IS NULL GROUP BY orderMonth ORDER BY o.orderCreated DESC LIMIT 10

Which gives the following error

Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'newby_cms.o.orderCreated' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

The above error only occurs on our staging site and works when run locally. Both are working off the same database import data, and have the exact same files, which should all be core/shop files so haven't been edited.

Here is the full debug log

Debug Message - Perch Runway 3.1.1
[1] SELECT u.*, r.* FROM perch3_users u, perch3_user_roles r WHERE u.roleID=r.roleID AND u.userEnabled=1 AND u.userID=1 AND u.userHash='5f4960f22129d833a6ce8615cb1c4688' LIMIT 1
UPDATE perch3_users SET userHash='b0529e0837579b7f46779ef5dabb9c3b' WHERE userID='1'
[60] SELECT settingID, settingValue, userID FROM perch3_settings WHERE userID=1 OR userID=0 ORDER BY userID ASC
[16] SELECT itemValue FROM perch3_menu_items WHERE itemType='app'
[1] SELECT SQL_CALC_FOUND_ROWS DISTINCT * FROM perch3_shop_products WHERE 1=1 AND productDeleted IS NULL ORDER BY title ASC LIMIT 0, 1
[61] SELECT FOUND_ROWS() AS `count`
[9] SELECT statusKey FROM perch3_shop_order_statuses WHERE statusDeleted IS NULL ORDER BY statusIndex ASC
[3] SELECT o.orderID, o.orderCreated, o.orderInvoiceNumber, o.orderTotal, o.currencyID, o.orderExchangeRate, c.customerID, c.customerFirstName, c.customerLastName, cur.* FROM perch3_shop_orders o, perch3_shop_customers c, perch3_shop_currencies cur WHERE o.customerID=c.customerID AND o.currencyID=cur.currencyID AND o.orderStatus IN ('paid', 'processing', 'cancelled', 'dispatched', 'returned', 'partial_refund', 'refunded') AND o.orderDeleted IS NULL ORDER BY o.orderCreated DESC LIMIT 10
SELECT DATE_FORMAT(o.orderCreated, "%Y-%m-01") AS orderMonth, SUM(o.orderTotal / o.orderExchangeRate) AS revenue FROM perch3_shop_orders o WHERE o.orderStatus IN ('paid', 'processing', 'cancelled', 'dispatched', 'returned', 'partial_refund', 'refunded') AND o.orderDeleted IS NULL GROUP BY orderMonth ORDER BY o.orderCreated DESC LIMIT 10
Invalid query: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'newby_cms.o.orderCreated' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
[1] SELECT mi.*, p.privKey FROM perch3_menu_items mi LEFT JOIN perch3_user_privileges p ON mi.privID=p.privID WHERE mi.itemActive=1 AND mi.parentID=0 ORDER BY itemOrder ASC LIMIT 0, 1
[nil] SELECT mi.*, p.privKey FROM perch3_menu_items mi LEFT JOIN perch3_user_privileges p ON mi.privID=p.privID WHERE mi.itemActive=1 AND mi.parentID=1 ORDER BY mi.itemOrder ASC
[1] SELECT itemTitle FROM perch3_menu_items WHERE itemType='app' AND itemValue='perch_shop' LIMIT 1
[3] SELECT mi.*, p.privKey FROM perch3_menu_items mi LEFT JOIN perch3_user_privileges p ON mi.privID=p.privID WHERE mi.itemActive=1 AND mi.parentID=0 ORDER BY itemOrder ASC LIMIT 1, 10
[4] SELECT mi.*, p.privKey FROM perch3_menu_items mi LEFT JOIN perch3_user_privileges p ON mi.privID=p.privID WHERE mi.itemActive=1 AND mi.parentID=19 ORDER BY mi.itemOrder ASC
[5] SELECT mi.*, p.privKey FROM perch3_menu_items mi LEFT JOIN perch3_user_privileges p ON mi.privID=p.privID WHERE mi.itemActive=1 AND mi.parentID=18 ORDER BY mi.itemOrder ASC
[4] SELECT mi.*, p.privKey FROM perch3_menu_items mi LEFT JOIN perch3_user_privileges p ON mi.privID=p.privID WHERE mi.itemActive=1 AND mi.parentID=2 ORDER BY mi.itemOrder ASC
File: /addons/apps/perch_shop/modes/shop.post.php
Array
(
    [0] => revenue
    [1] => customers
    [2] => orders
)
[1] SELECT * FROM perch3_shop_currencies WHERE currencyActive=1 AND currencyID=47
[0] Using template: /addons/apps/perch_shop/templates/shop/stats/revenue.html
[3] Using template: /addons/apps/perch_shop/templates/shop/stats/orders.html
Mode: shop
Queries: 17
Memory: 1.2629
Array
(
    [type] => 2
    [message] => count(): Parameter must be an array or an object that implements Countable
    [file] => /home/forge/newby.rootstaging.uk/public/admin/addons/apps/perch_shop/modes/shop.post.php
    [line] => 209
)

and the basic diagnostic

Perch Runway: 3.1.1, PHP: 7.2.5-1+ubuntu18.04.1+deb.sury.org+1, MySQL: mysqlnd 5.0.12-dev - 20150407 - $Id: 38fea24f2847fa7519001be390c98ae0acafe387 $, with PDO
Server OS: Linux, fpm-fcgi
Installed apps: content (3.1.1), assets (3.1.1), categories (3.1.1), perch_blog (5.6.1), perch_forms (1.11), perch_shop_orders (1.2.6), perch_shop_products (1.2.6), perch_shop (1.2.6), perch_members (1.6.4), pipit_catalog (1.2.1), perch_mailchimp (3.1)
App runtimes: <?php $apps_list = [ 'perch_blog', 'perch_members', 'perch_shop', 'perch_forms', 'perch_mailchimp' ];
PERCH_LOGINPATH: /admin
PERCH_PATH: /home/forge/newby.rootstaging.uk/public/admin
PERCH_CORE: /home/forge/newby.rootstaging.uk/public/admin/core
PERCH_RESFILEPATH: /home/forge/newby.rootstaging.uk/public/admin/resources
Image manipulation: GD
PHP limits: Max upload 32M, Max POST 32M, Memory: 512M, Total max file upload: 32M
F1: 3b606135b33e6a102526838f4152a807
Resource folder writeable: Yes
HTTP_HOST: newby.rootstaging.uk
DOCUMENT_ROOT: /home/forge/newby.rootstaging.uk/public
REQUEST_URI: /admin/core/settings/diagnostics/
SCRIPT_NAME: /admin/core/settings/diagnostics/index.php