CODEWITHSUNDEEP
sqlteradata
TheIntrepidSpiff
TheIntrepidSpiff

Reputation: 189

SQL Show defined range in one column and results in a second column

This may be simple, but I just can't figure it out. I tried using multiple queries and UNION but I just get one column with the Avg Quantities but no ColumnA as a reference to which range the average is associated with.

I have two fields in a table, ColumnA and ColumnB in TABLE1. ColumnA is the price and ColumnB has the quantity of items for that price.

I want to define ranges in ColumnA, such as "where ColumnA between 11 and 20 as Range 1" "where ColumnB between 21 and 30" and so on....and then get the average quantity of items for products within that price range.

For instance, the query results would look like:

ColumnA | ColumnB

Range 1 | 15.9

Range 2 | 16.7

Range 3 | 19.8

Any input would be greatly appreciated!

Upvotes: 0

Views: 96

Answers (1)

dnoeth
dnoeth

Reputation: 60462

If those ranges are not overlapping:

select 
   case
      when ColumnA between 11 and 20 then 'Range 1'
      when ColumnA between 21 and 30 then 'Range 2'
      ...
   end,
   avg(ColumnB)
from tab
group by 1

Upvotes: 1

Related Questions