CODEWITHSUNDEEP
phpmysqllaravel
arun kumar
arun kumar

Reputation: 1

Syntax error or access violation: 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...)

SQL Error

SQLSTATE[42000]: Syntax error or access violation: 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause (SQL: SELECT sum(vehicles.total_amount) as TotalAmount, COALESCE(CONCAT(payment_mode.payment_name, '(', ROUND( SUM(vehicles.total_amount)*100/Total.TotalAmount, 2),'%)'),0) as Percent FROM vehicles LEFT OUTER join payment_mode ON payment_mode.id = vehicles.pay_mode_id LEFT OUTER JOIN (SELECT SUM(vehicles.total_amount) as TotalAmount, flag from vehicles) as Total ON Total.flag=vehicles.flag WHERE vehicles.created_at > '2020-03-25 00:01:01' and vehicles.created_at < '2020-03-25 23:59:59' GROUP BY vehicles.pay_mode_id)

SQL Query

SELECT sum(vehicles.total_amount) as TotalAmount,
        COALESCE(CONCAT(payment_mode.payment_name, '(', ROUND( SUM(vehicles.total_amount)*100/Total.TotalAmount, 2),'%)'),0) as Percent
FROM `vehicles`
LEFT OUTER join payment_mode ON payment_mode.id = vehicles.pay_mode_id
LEFT OUTER JOIN (SELECT SUM(vehicles.total_amount) as TotalAmount, flag from vehicles) as Total ON Total.flag=vehicles.flag
WHERE vehicles.created_at > '$today' and vehicles.created_at < '$todays'
GROUP BY vehicles.pay_mode_id

this is my code, even this code is working well on MySQL but when I put into Laravel its shows errors.

Upvotes: 0

Views: 510

Answers (1)

Akina
Akina

Reputation: 42641

Your subquery (SELECT SUM(vehicles.total_amount) as TotalAmount, flag from vehicles) as Total is wrong - add GROUP BY flag to it:

SELECT sum(vehicles.total_amount) as TotalAmount,
        COALESCE(CONCAT(payment_mode.payment_name, '(', ROUND( SUM(vehicles.total_amount)*100/Total.TotalAmount, 2),'%)'),0) as Percent
FROM `vehicles`
LEFT OUTER join payment_mode ON payment_mode.id = vehicles.pay_mode_id
LEFT OUTER JOIN ( SELECT SUM(vehicles.total_amount) as TotalAmount, flag 
                  from vehicles
                  GROUP BY flag ) as Total ON Total.flag=vehicles.flag
WHERE vehicles.created_at > '$today' and vehicles.created_at < '$todays'
GROUP BY vehicles.pay_mode_id

Upvotes: 1

Related Questions