Trying to aggregate price and orders , make the output less granular

Johnny2x 0
Viewed 23

Only three months into SQL, so excuse the level of ignorance. Got stuck on something I'm not sure the process I'm following is correct.

Got to values, price and quantity. Trying to average the price or create smaller ranges, then add the quantities within that range.

Price   Quantity
20289.7 0.001
21320   1.798
20259.4 1.724
20365   2.1
21066.6 0.055
20517.8 0.002
20836.9 0.037

Lets say for every $50 range on left, how many orders are placed in that range. Seen something like the following

SELECT * 
FROM (
    SELECT CASE
    WHERE price BETWEEN 15000 AND 18000 then '10 -18'
    WHERE price BETWEEN 18000 AND 19000 then '18 - 19'
    WHERE price BETWEEN 19000 AND 20000 then '19 - 20'

but I'm not sure if this is the correct path.

postgresql
1 Answers

This is the correct path, but syntax is incorrect:

SELECT
    CASE WHEN price < 15000 THEN
        '<15'
    WHEN price BETWEEN 15000 AND 18000 THEN
        '15 -18'
    WHEN price BETWEEN 18001 AND 19000 THEN
        '18 - 19'
    WHEN price BETWEEN 19001 AND 20000 THEN
        '19 - 20'
    ELSE
        '>20'
    END AS price_range,
    sum(quantity) AS sum_quantity
FROM
    orders
GROUP BY
    CASE WHEN price < 15000 THEN
        '<15'
    WHEN price BETWEEN 15000 AND 18000 THEN
        '15 -18'
    WHEN price BETWEEN 18001 AND 19000 THEN
        '18 - 19'
    WHEN price BETWEEN 19001 AND 20000 THEN
        '19 - 20'
    ELSE
        '>20'
    END;

always provide edge cases, in case a new price gets inserted not within the range.

Saya0
Related