CODEWITHSUNDEEP
sqlpostgresql
samuel_C
samuel_C

Reputation: 497

sql query that groups different items into buckets

I am trying to write a query that returns the count of items whose price falls into certrain buckets:

For example if my table is:

item_name | price
i1        | 2
i2        | 12
i3        | 4
i4        | 16
i5        | 6

output:

range   | number of item
0 - 10  |  3
10 - 20 |  2

The way I am doing it so far is 

SELECT count(*)
FROM my_table
Where price >=0
and price <10

then 

SELECT count(*)
FROM my_table
Where price >=10
and price <20

and then copy pasting my results each time into excel.

Is there an automatic way to do this in an sql query?

Upvotes: 47

Views: 96836

Answers (5)

prashant.kr.mod
prashant.kr.mod

Reputation: 1702

Using decode in orderBy:

select
case when price >= 0 and price < 10    then "  0 - 10"
           when price > 10 and price <= 50   then " 10+ - 50"
           when price > 50 and price <= 100  then " 50+ - 100"
           else "over 100"
end As PriceRange,
count(item_name) as ItemTotal,
customer_name,
cust_address,
cust_sigid
from YourTable
group by 
case when price >= 0 and price < 10    then "  0 - 10"
       when price > 10 and price <= 50   then " 10+ - 50"
       when price > 50 and price <= 100  then " 50+ - 100"
       else "over 100"
end
order by
decode((case when price >= 0 and price < 10    then "  0 - 10"
           when price > 10 and price <= 50   then " 10+ - 50"
           when price > 50 and price <= 100  then " 50+ - 100"
           else "over 100"
end),"  0 - 10", 1, " 10+ - 50", 2, " 50+ - 100", 3, "over 100", 4, 5) ASC;

Decode works in a fashion in which it takes expression in the first argument and then subsequent key-value pairs and then finally a default value.

Documentation:

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions040.htm

Upvotes: 1

Harshith Bolar
Harshith Bolar

Reputation: 825

Here's a simple mysql solution. First, calculate the bucket index based on the price value.

select *, floor(price/10) as bucket from mytable

+------+-------+--------+
| name | price | bucket |
+------+-------+--------+
| i1   |     2 |      0 |
| i2   |    12 |      1 |
| i3   |     4 |      0 |
| i4   |    16 |      1 |
| i5   |     6 |      0 |
+------+-------+--------+

Next, group by the bucket index and calculate sum of price and format the prange column.

select concat(bucket * 10, '-', (bucket * 10 - 1) + 10) as prange, count(price)
from  (select *, floor(price/10) as bucket from mytable) t1 
group by bucket;

+--------+--------------+
| prange | count(price) |
+--------+--------------+
| 0-9    |            3 |
| 10-19  |            2 |
+--------+--------------+

Upvotes: 3

DRapp
DRapp

Reputation: 48149

An expanded option from what Kerrek described, you can do you grouping based on a case/when

select
      case when price >= 0 and price <= 10    then '  0 - 10'
           when price > 10 and price <= 50   then ' 10+ - 50'
           when price > 50 and price <= 100  then ' 50+ - 100'
           else 'over 100'
      end PriceRange,
      count(*) as TotalWithinRange
   from
      YourTable
   group by 1

Here, the "group by 1" represents the ordinal column in your select statement... in this case, the case/when as TotalWithinRange.

Upvotes: 66

JK0124
JK0124

Reputation: 395

A bit of modification of DRapp's code...

select
case when price >= 0 and price < 10    then "  0 - 10"
           when price > 10 and price <= 50   then " 10+ - 50"
           when price > 50 and price <= 100  then " 50+ - 100"
           else "over 100"
end As PriceRange,
count(item_name) as ItemTotal
from YourTable
group by 
case when price >= 0 and price < 10    then "  0 - 10"
           when price > 10 and price <= 50   then " 10+ - 50"
           when price > 50 and price <= 100  then " 50+ - 100"
           else "over 100"
end;

Upvotes: 2

Kerrek SB
Kerrek SB

Reputation: 477358

You can try grouping by 10 units of price:

SELECT COUNT(*) AS tally,
       FLOOR(price/10) AS prange,
       CONCAT(10*FLOOR(price/10), "-", 10*FLOOR(price/10)+9) AS rstr
FROM my_table
GROUP BY prange;

Upvotes: 37

Related Questions