Daniel James Canil - 1 year ago 107

SQL Question

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 Source

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
```