I got the error like Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064

0
votes

My query is like ..

SELECT  oi.`sku`,oi.`product_id`, count(*) as `count1`
                FROM `order_item` AS oi RIGHT JOIN `products` AS p ON oi.`product_id` =  p.`entity_id` WHERE oi.`product_id` IN (1234,4556,7854)
                GROUP BY oi.`sku` ORDER BY `count1` DESC

i got the error like below..

PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') GROUP BY sfoi.sku ORDER BY count1 DESC' at line 4'

Please can you guys explain this ,is there any wrong on my query?

1
phpmysqlmagentoselectjoin
no space at all...actually those values are passed like dynamically. – user5819857

1 Answers

0
votes

You should GROUP BY by two columns: oi.sku,oi.product_id

SELECT oi.sku,oi.product_id, count(*) as count1 
FROM order_item AS oi 
RIGHT JOIN products AS p ON oi.product_id = p.entity_id 
WHERE oi.product_id IN (1234,4556,7854) 
GROUP BY oi.sku,oi.product_id
ORDER BY count1 DESC