SQL Query That Groups Different Items into Buckets

sql query that groups different items into buckets

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.

How to group data into buckets in Microsoft SQL

SELECT NAME, 
CASE WHEN [BASE/DAY] <= 325 THEN '300 <= 325'
     WHEN [BASE/DAY] <= 350 THEN '325 <= 350'
     WHEN [BASE/DAY] <= 400 THEN '350 <= 400'
END AS BUCKET,
[BASE/DAY]
FROM
(
    SELECT NAME, ROUND([DR# BASE]/DAYS_WORKED,0) AS 'BASE/DAY' FROM MYTABLE
) T
ORDER BY 1, 2, 3

Group MySQL rows into buckets based on Min value

SELECT t1.Product, 
       t3.Treshold, 
       SUM(t1.Price <= t3.Treshold) GroupA, 
       SUM(t1.Price > t3.Treshold) GroupB
FROM test t1
JOIN ( SELECT Product, MIN(Price) * 1.1 Treshold
       FROM test t2
       GROUP BY 1 ) t3 USING (Product)
GROUP BY 1

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=be61a9a526477ae821fa38c9cb450e8d

Grouping/aggregating SQL results into 1-hour buckets

You need to have a pre-populated table (or a function returning a table result set) to join with, that contains all the 1-hour slots you want in your result.

Then you do a OUTER JOIN with that, and you should get them all.

Something like this:

SELECT SLOT_HOUR, SUM(order_id)
FROM
    ONEHOURSLOTS
    LEFT JOIN ORDERS ON DATEPART(hh, order_date) = SLOT_HOUR
GROUP BY SLOT_HOUR

Divide rows into buckets given a group (bin) size in postgresql

you can calculate it by row_number, eg. for 100 items divide by 100 :

with temp_data as (
select * from generate_series(1,1000)
)
  
  select
  *,
  ((row_number() over() -1) / 100)::int +1 as bucket_nr
  from temp_data

https://www.db-fiddle.com/f/uBQ7stu6rvN9kdBSRLTFb6/0

---

Related Topics