问题描述:

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.

您可能感兴趣的文章：

- Where is a comprehensive reference for the Spark SQL Windowing / analytic functions?
- python - speeding up dataframe column assignment with lambdas
- bower - How to use existing libraries in reactjs/webpack like GSAP?
- wordpress - subproduct is not adding into cart in woocommerce product bundle
- java - Playing Multiple mp3 files using android media player , at the same time . it jumps to the last mp3 on the list
- php - Reading a specific line from a text file
- clr - Any implementation of an Unrolled Linked List in C#?
- Finding Hudson Log Files
- Forward to a payment-gateway together with POST data using cURL (or any other PHP server side solution)
- WCF in Winforms app - is it always single-threaded?

随机阅读：

**推荐内容**-

**热点内容**