Error in Magento: Column not found: 1054 Unknown column 'FLOOR((ROUND((e.min_price) * 1, 2)) / 10000) + 1'

Error in Magento: Column not found: 1054 Unknown column 'FLOOR((ROUND((e.min_price) * 1, 2)) / 10000) + 1'

October 7, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Error in Magento: Column not found: 1054 Unknown column 'FLOOR((ROUND((e.min_price) * 1, 2)) / 10000) + 1'

Solving the Magento Column Not Found Error

Hello! I recently encountered an error in my Magento installation that caused me some trouble. The error message read:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'FLOOR((ROUND((e.min_price) * 1, 2)) / 10000) + 1' in 'order clause'.

The query that triggered this error was:

SELECT FLOOR((ROUND((e.min_price) * 1, 2)) / 10000) + 1 AS `range`, COUNT(*) AS `count`
FROM `catalog_product_index_price` AS `e`

Understanding the Issue

The problem lies in this part of the query:

GROUP BY FLOOR((ROUND((e.min_price) * 1, 2)) / 10000) + 1
ORDER BY `FLOOR((ROUND((e`.`min_price) * 1, 2)) / 10000) + 1` ASC

Magento is trying to group by and order the results using a calculated column that doesn’t actually exist in the database. This leads to the “Column not found” error.

Resolving the Error

To fix this issue, we need to ensure that the calculated values used for grouping and ordering are present as actual columns in the query result set. Here’s how I resolved it:

  1. First, let’s simplify the calculation by breaking it down into separate steps. This will make the code more readable.

  2. Create a new table or subquery that calculates the rounded min_price per 10000 with proper column names.

  3. Join this new table to your main catalog_product_index_price table and perform the grouping and ordering operations on the existing, named columns in the joined result set.

Here’s an example of how the corrected query might look:

SELECT t.range, COUNT(*) AS count
FROM (
    SELECT FLOOR(ROUND(min_price * 1, 2) / 10000) + 1 AS range
    FROM `catalog_product_index_price`
    WHERE min_price IS NOT NULL
) AS t
JOIN catalog_product_index_price e ON t.range = (FLOOR((ROUND(e.min_price * 1, 2)) / 10000))
WHERE e.website_id = '1'
GROUP BY t.range
ORDER BY t.range ASC;

Best Practices for Magento Queries

When working with complex Magento queries, always keep the following best practices in mind:

  • Avoid using calculated columns directly in GROUP BY or ORDER BY clauses without creating them as actual named columns first.
  • Use subqueries or CTEs (Common Table Expressions) to simplify complex calculations and improve code readability.
  • Ensure that your joins are performing efficiently by minimizing the number of tables involved and using proper indexing.

Conclusion

Diagnosing and resolving this “Column not found” error in Magento required a deep understanding of MySQL syntax, query optimization techniques, and familiarity with Magento’s database schema. By following best practices and properly structuring our queries, we can prevent such errors from occurring in the future.

Remember to always test your changes thoroughly on a staging environment before deploying them to production. If you’re looking for more advanced solutions or expert guidance, feel free to check out Laravel Company’s Magento development services.

Happy coding!

Catalog Database Orders Pricing

Our network