'One column group by order value

The table name is "OrderDetails" and columns are given below:

| OrderDetailID | Order Value |
| --- | -- |
| 1 | 1000 |
| 2 | 1200 |
| 3 | 1500 |
| 4 | 2000 |
| 5 | 2500 |
| 6 | 3000 |
| 7 | 5000 |
| 8 | 7000 |
| 9 | 8500 |
| 10 | 12000 |

I'm trying to get details as follows:

| Order Value Group | Count |
| --- | --- |
| Between 1 to 2000 | 4 |
| Between 2001 to 4000 | 2 |
| Between 4001 to 7000 | 2 |
| Between 7001 to 10000 | 1 |
| Above 10001 | 1 |
sql


Solution 1:[1]

WITH OrderDetails(OrderDetailID,Value) AS
 (
    SELECT 1, 1000 UNION ALL
    SELECT 2, 1200 UNION ALL
    SELECT 3, 1500 UNION ALL
    SELECT 4, 2000 UNION ALL
    SELECT 5, 2500 UNION ALL
    SELECT 6, 3000 UNION ALL
    SELECT 7, 5000 UNION ALL
    SELECT 8 ,7000 UNION ALL
    SELECT 9 ,8500 UNION ALL
    SELECT 10 ,12000
)
SELECT 
  CASE
    WHEN C.Value BETWEEN 1 AND 2000 THEN 'Between 1 to 2000'
    WHEN C.VALUE BETWEEN 2001 AND 4000 THEN 'Between 2001 to 4000'
    WHEN C.VALUE BETWEEN 4001 AND 7000 THEN 'Between 4001 to 7000'
    WHEN C.VALUE BETWEEN 7001 AND 10000 THEN 'Between 7001 to 10000' 
    WHEN C.VALUE >10000 THEN 'Above 10001'
  END AS GRP, 
 COUNT(C.OrderDetailID)AS CNTT
 FROM OrderDetails AS C
GROUP BY
CASE
   WHEN C.Value BETWEEN 1 AND 2000 THEN 'Between 1 to 2000'
   WHEN C.VALUE BETWEEN 2001 AND 4000 THEN 'Between 2001 to 4000'
   WHEN C.VALUE BETWEEN 4001 AND 7000 THEN 'Between 4001 to 7000'
   WHEN C.VALUE BETWEEN 7001 AND 10000 THEN 'Between 7001 to 10000' 
   WHEN C.VALUE >10000 THEN 'Above 10001'
END

Solution 2:[2]

would go with case when + group by:

select `Order Value Group`, Count(`Order Value Group`) from (
  select case
    when `Order Value` >= 1 and `Order Value` <=  2000 then 'Between 1 to 2000'
    when                        `Order Value` <=  4000 then 'Between 2001 to 4000'
    when                        `Order Value` <=  7000 then 'Between 4001 to 7000'
    when                        `Order Value` <= 10000 then 'Between 7001 to 10000'
    else 'Above 10000'
    end as `Order Value Group`
  from OrderDetails) a
  group by `Order Value Group`
  order by Count(`Order Value Group`) desc
  ;

example with your numbers with MySQL 5.6 on SQL Fiddle

Solution 3:[3]

Please try this. This query is compatible in mysql also.

-- SQL Server
SELECT CASE WHEN order_value BETWEEN 1 AND 2000
                 THEN 'BETWEEN 1 AND 2000'
              WHEN order_value BETWEEN 2001 AND 4000
                 THEN 'BETWEEN 2001 AND 4000'
              WHEN order_value BETWEEN 4001 AND 7000
                 THEN 'BETWEEN 4001 AND 7000'
              WHEN order_value BETWEEN 7001 AND 10000
                 THEN 'BETWEEN 7001 AND 10000'
              ELSE 'Above 10001'
         END Order_value_group
     , COUNT(OrderDetailID) count_val    
FROM OrderDetails
GROUP BY CASE WHEN order_value BETWEEN 1 AND 2000
                 THEN 'BETWEEN 1 AND 2000'
              WHEN order_value BETWEEN 2001 AND 4000
                 THEN 'BETWEEN 2001 AND 4000'
              WHEN order_value BETWEEN 4001 AND 7000
                 THEN 'BETWEEN 4001 AND 7000'
              WHEN order_value BETWEEN 7001 AND 10000
                 THEN 'BETWEEN 7001 AND 10000'
              ELSE 'Above 10001'
         END

Please check from url https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=6630cf41c7710d94f721f1db6cb6b574

In mysql Alias is used in group by clause. Other RDBMS like sql server doesn't support alias in GROUP BY clause.

-- MySQL(v5.8)
SELECT CASE WHEN order_value BETWEEN 1 AND 2000
                 THEN 'BETWEEN 1 AND 2000'
              WHEN order_value BETWEEN 2001 AND 4000
                 THEN 'BETWEEN 2001 AND 4000'
              WHEN order_value BETWEEN 4001 AND 7000
                 THEN 'BETWEEN 4001 AND 7000'
              WHEN order_value BETWEEN 7001 AND 10000
                 THEN 'BETWEEN 7001 AND 10000'
              ELSE 'Above 10001'
         END Order_value_group
     , COUNT(OrderDetailID) count_val    
FROM OrderDetails
GROUP BY Order_value_group

For MySQL Please check from url https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=97b042ea116577e580ffda5c6a7723be

Solution 4:[4]

Here is your answer

WITH OrderDetails(OrderDetailID, Value) AS
    (
    SELECT 1, 1000 UNION ALL
    SELECT 2, 1200 UNION ALL
    SELECT 3, 1500 UNION ALL
    SELECT 4, 2000 UNION ALL
    SELECT 5, 2500 UNION ALL
    SELECT 6, 3000 UNION ALL
    SELECT 7, 5000 UNION ALL
    SELECT 8 ,7000 UNION ALL
    SELECT 9 ,8500 UNION ALL
    SELECT 10 ,12000
    ),
OrderRange(SeqNo, MinValue, MaxValue, Order_Value_Group) AS 
    (
    SELECT 1, 1 , 2000 , 'Between 1 to 2000' UNION ALL
    SELECT 2, 2001 , 4000 , 'Between 2001 to 4000' UNION ALL
    SELECT 3, 4001 , 7000 , 'Between 4001 to 7000' UNION ALL
    SELECT 4, 7001 , 10000 , 'Between 7001 to 10000'  UNION ALL
    SELECT 5, 10001,100000 , 'Above 10001' 
    )

SELECT R.Order_Value_Group,COUNT(1) AS Count
FROM OrderDetails I INNER JOIN OrderRange R ON I.Value BETWEEN R.MinValue AND R.MaxValue 
GROUP BY r.SeqNo, R.Order_Value_Group
ORDER BY r.SeqNo

Its easy to understand.

Solution 5:[5]

You can apply aggregate function count with between keyword as follows:

  1. select count(orderValue) form OrderDetails od where od.orderValue between 1 and 2000;

for other results just replace between values.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Sergey
Solution 2 Isis Santos Costa
Solution 3
Solution 4 Vipul Zadafiya
Solution 5 Sujit Sharma

Related Questions