One column group by order value-CodePudding

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 |

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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

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

for other results just replace between values.