CODEWITHSUNDEEP
sql-servert-sql
Adam
Adam

Reputation: 6152

Select how often a column value occurs within a pre-defined range

I want to select the number of occurrences of values of the [price] field in a custom defined range:

My price ranges are: 

< 10  
10-20  
20-50  
> 50

So if prices would be 3,4,11 and 20 the result would be:

< 10 (2)  
10-20 (1)  
21-50 (1)  
> 50 (0)

Here's the table definition:

CREATE TABLE [dbo].[products](
[id] [int] IDENTITY(1,1) NOT NULL,
[price] [decimal](8, 0) NULL,
[createdate] [datetime] NOT NULL,
CONSTRAINT [PK_products] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[products] ADD  CONSTRAINT [DF_products_createdate]  DEFAULT (getdate()) FOR [createdate]
GO

Upvotes: 0

Views: 122

Answers (2)

bobs
bobs

Reputation: 22204

You can do this.

;WITH Ranges_CTE ([sequence], [range], [lower_limit], [upper_limit]) AS
  (
   SELECT 1,'<10', 0, 9
    UNION
    SELECT 2,'10-20', 10, 19
    UNION
    SELECT 3, '20-50', 20, 50
    UNION
    SELECT 4, '>50', 51, 1000
   )

SELECT r.range, count(p.id) AS ROW_COUNT
FROM ranges_cte r
LEFT OUTER JOIN products p ON p.price BETWEEN r.lower_limit AND r.upper_limit
GROUP BY r.range, r.sequence
ORDER BY r.sequence

Note that your ranges are ambiguous. You show 20-50 in one place and 21-50 elsewhere. The data seems to indicate that 20-50 is correct. But, then price 20 would fit in 10-20 and 20-50. I assumed 10-20 had an upper limit of 19.

Upvotes: 0

AaronLS
AaronLS

Reputation: 38364

With 
Categorized as
(
 Select 
 CASE When Price < 10 Then '<10'
      When Price >= 10 and Price < 20 Then '[10-20)'
      Else '>20'
 END as Category
 From SomeTable
)
Select Category, Count(*) From Categorized
Group By Category

Something like this. Good luck.

Upvotes: 1

Related Questions