Daniel James Canil Daniel James Canil - 6 months ago 29
SQL Question

Group by Case Statement MySQL

I am trying to group by the CASE statement but not having much luck. I have an orders table that I am trying to group the orders by total value for the month and categorise them based on their value.

SELECT
CASE
WHEN sum(order_total_price) IS NULL
THEN 'Unknown'
WHEN sum(order_total_price) <= 1000
THEN 'Not more than 1,000'
WHEN sum(order_total_price) <= 2000
THEN 'Between 1,001 and 2000'
WHEN sum(order_total_price) <= 3000
THEN 'Between 2001 and 3000'
WHEN sum(order_total_price) <= 4000
THEN 'Between 3001 and 4000'
WHEN sum(order_total_price) <= 5000
THEN 'Between 4001 and 5000'
ELSE 'Over 5000'
END
AS total_sales,
COUNT(*) as total
FROM orders
WHERE YEAR(order_time)=2014 and MONTH(order_time)=07

GROUP BY total_sales

Answer

As Jim Garrison said,

GROUP BY 1

would be best.

As an alternative, below will also work if you are not comfortable with numbers in group by. Put your original query without GROUP BY as inner query. Use the count and group by in an outer query.

  SELECT A.TOTAL_SALES, COUNT(1) FROM
  (SELECT 
      CASE
          WHEN sum(order_total_price) IS NULL
            THEN 'Unknown'
          WHEN sum(order_total_price) <= 1000
            THEN 'Not more than 1,000'
          WHEN sum(order_total_price) <= 2000
            THEN 'Between 1,001 and 2000'
          WHEN sum(order_total_price) <= 3000
            THEN 'Between 2001 and 3000'
          WHEN sum(order_total_price) <= 4000
            THEN 'Between 3001 and 4000'
          WHEN sum(order_total_price) <= 5000
            THEN 'Between 4001 and 5000'
          ELSE 'Over 5000'
        END
          AS  total_sales       
      FROM orders
      WHERE YEAR(order_time)=2014 and MONTH(order_time)=07

      ) AS A
      GROUP BY A.total_sales
Comments