问题描述:

Hi I want to show the Result set in ascending order. I have created the SQL FIDDLE for the same.

select amount_range as amount_range, count(*) as number_of_items,

sum(amount) as total_amount

from (

select *,case

when amount between 0.00 and 2500.00 then '<=$2,500.00'

when amount between 2500.01 and 5000.00 then '$2,500.01 - $5,000.00'

when amount between 5000.01 and 7500.00 then '$5,000.01 - $7,500.00'

when amount between 7500.01 and 10000.00 then '$7,500.01 - $10,000.00'

else '>$10,000.01' end as amount_range

from Sales ) a

group by amount_range order by amount_range;

My Results should be like

<=$2,500.00 4 5000

$2,500.01 - $5,000.00 3 12000

$5,000.01 - $7,500.00 2 13000

$7,500.01 - $10,000.00 1 10000

>$10,000.01 1 15000

网友答案:

The easiest method will be to sort off of a value in each grouping, for example the minimum amount:

select amount_range as amount_range,
  count(*) as number_of_items,
  sum(amount) as total_amount
from (
  select *,case
    when amount between 0.00 and 2500.00 then '<=$2,500.00'
    when amount between 2500.01 and 5000.00 then '$2,500.01 - $5,000.00'
    when amount between 5000.01 and 7500.00 then '$5,000.01 - $7,000.00'
    when amount between 7500.01 and 10000.00 then '$7,500.01 - $10,000.00'
    else '>$10,000.01' end as amount_range
  from Sales ) a
group by amount_range
order by min(amount);

In Postgres, your subquery could also return an array where the first element is the desired position and the second is the string describing the bucket. Then, the outer query can ORDER BY your positioning value.

select amount_range[2] as amount_range,
  count(*) as number_of_items,
  sum(amount) as total_amount
from (
  select *,case
    when amount between 0.00 and 2500.00 then ARRAY['1','<=$2,500.00']
    when amount between 2500.01 and 5000.00 then ARRAY['2','$2,500.01 - $5,000.00']
    when amount between 5000.01 and 7500.00 then ARRAY['3', '$5,000.01 - $7,000.00']
    when amount between 7500.01 and 10000.00 then ARRAY['4', '$7,500.01 - $10,000.00']
    else ARRAY['5','>$10,000.01'] end as amount_range
  from Sales ) a
group by amount_range
order by amount_range[1];

The first method happens to be simpler for your example. The second method would be useful if you were bucketing by something more complicated than ranges.

相关阅读:
Top