Group By Price Ranges
-
Hi there,
I have a table with lots of products, each one has a price. I want to create a view that will show the count of items that fall within these ranges:
£0 to £50
£50 to £100
£100 to £500
£500 to £1000
Above £1000
I could use this:
SELECT '£0 to £50' AS Range, COUNT(*) AS Count
FROM dbo.item AS R1
WHERE (price BETWEEN 0 AND 50)
UNION
SELECT '£50 to £100' AS Range, COUNT(*) AS Count
FROM dbo.item AS R2
WHERE (price BETWEEN 51 AND 100)
But is there a neater more compact way?
Cheers,
Steve
-
This is NOT a direct answer to your question, but perhaps it will get you thinking towards a "compact" solution.
This requires the use of a numbers or "tally" table described elsewhere on this site.
Enjoy ....
DECLARE @t TABLE (price MONEY)
INSERT @t
SELECT 5 UNION ALL
SELECT 3 UNION ALL
SELECT 50 UNION ALL
SELECT 52 UNION ALL
SELECT 155 UNION ALL
SELECT 156 UNION ALL
SELECT 495 UNION ALL
SELECT 500 UNION ALL
SELECT 505
SELECT
CAST(n.N - 1 AS VARCHAR(9)) + '-' + CAST(n2.N AS VARCHAR(9)) AS range
,COUNT(CASE WHEN price BETWEEN n.N - 1 AND n2.N THEN price END) AS rangeCount
FROM @t
CROSS JOIN dbo.tally AS n
CROSS JOIN dbo.tally AS n2
WHERE
n.N IN (1,51,101,501,1001)
AND n2.N IN (50,100,500,1000,999999999)
AND n.N < n2.N
GROUP BY
n.N, n2.N
ORDER BY
n.N - 1
,n2.N
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
Try joining to a tables of ranges
CREATE TABLE Ranges(RangeFrom money NOT NULL,
RangeTo money NOT NULL,
Range varchar(20) NOT NULL,
PRIMARY KEY CLUSTERED (RangeFrom, RangeTo));
INSERT INTO Ranges VALUES (0, 50, '£0 to £50');
INSERT INTO Ranges VALUES (50, 100, '£50 to £100');
INSERT INTO Ranges VALUES (100, 500, '£100 to £500');
INSERT INTO Ranges VALUES (500, 1000, '£500 to £1000');
INSERT INTO Ranges VALUES (1000, 999999999, 'Above £1000');
SELECT Range,COUNT(*) AS Count
FROM Ranges
INNER JOIN dbo.item ON price >= RangeFrom AND price < RangeTo
GROUP BY Range
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
@ Jason, you have started me off in an interesting read, thank you.
@ Mark, that looks like a very good solution to me. Nice and compact.
The aim of this is to show the price ranges available on a webpage, the user is expected to click a link on the required range which will filter the recordset. I am using a stored procedure to handle this. It is a rather complicated solution, one that handles dynamic sorting and other filters.
SELECT *FROM item
WHERE (@ManID IS NULL OR manufacturerID = @manID) AND
(@cat1 IS NULL OR cat1ID = @cat1) AND
(@cat2 IS NULL OR cat2ID = @Cat2) AND
(@cat3 IS NULL OR cat3ID = @cat3) AND
(@cat4 IS NULL OR cat4ID = @cat4) AND
(@cat5 IS NULL OR cat5ID = @cat5) AND
(@cat6 IS NULL OR cat6ID = @cat6) AND
(@cat7 IS NULL OR cat7ID = @cat7) AND
(@cat8 IS NULL OR cat8ID = @cat8) AND
(@cat9 IS NULL OR cat9ID = @cat9) AND
(@cat10 IS NULL OR cat10ID = @cat10) AND
(@search IS NULL OR LOWER(item) LIKE '%' + LOWER(@search) + '%' OR LOWER([desc]) LIKE '%' + LOWER(@search) + '%')
ORDER BY featured DESC,
CASE @sortDir
WHEN 'desc' THEN
CASE @sortType
WHEN 1 THEN item
END
END
DESC,
CASE @sortDir
WHEN 'desc' THEN
CASE @sortType
WHEN 2 THEN price
END
END
DESC,
CASE @sortDir
WHEN 'desc' THEN
CASE @sortType
WHEN 3 THEN [order]
END
END
DESC,
CASE @sortDir
WHEN 'asc' THEN
CASE @sortType
WHEN 1 THEN item
END
END,
CASE @sortDir
WHEN 'asc' THEN
CASE @sortType
WHEN 2 THEN price
END
END,
CASE @sortDir
WHEN 'asc' THEN
CASE @sortType
WHEN 3 THEN [order]
END
END
Would I be better using dynamic sql to introduce the price where clause? Or can I be clever with a CASE statement instead?
Steve
-
I would think Mark's solution with a range table and using an INNER join would be better. I would also move the ORDERing out of the procedure and into the web app itself. Let the presentation layer handle presentation. 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
Hi Jason,
I am giving it a go now, I will see how I get on.
I am coding in ASP, hence putting the sorting in the stored procedure. I find it quicker to do it all in a stored procedure.
Cheers,
Steve
-
This is what I have got working for returning the price range counts:
SELECT Range,COUNT(DISTINCT(item)) AS CountFROM tblPriceRanges
INNER JOIN dbo.item ON price >= RangeFrom AND price < RangeTo
WHERE (@ManID IS NULL OR manufacturerID = @manID) AND
(@cat1 IS NULL OR cat1ID = @cat1) AND
(@cat2 IS NULL OR cat2ID = @Cat2) AND
(@cat3 IS NULL OR cat3ID = @cat3) AND
(@cat4 IS NULL OR cat4ID = @cat4) AND
(@cat5 IS NULL OR cat5ID = @cat5) AND
(@cat6 IS NULL OR cat6ID = @cat6) AND
(@cat7 IS NULL OR cat7ID = @cat7) AND
(@cat8 IS NULL OR cat8ID = @cat8) AND
(@cat9 IS NULL OR cat9ID = @cat9) AND
(@cat10 IS NULL OR cat10ID = @cat10) AND
(@search IS NULL OR LOWER(item) LIKE '%' + LOWER(@search) + '%' OR LOWER([desc]) LIKE '%' + LOWER(@search) + '%')
GROUP BY Range, CASE WHEN NOT @manID IS NULL THEN manufacturerID END, CASE WHEN NOT @cat1 IS NULL THEN cat1ID END, CASE WHEN NOT @Cat2 IS NULL THEN cat2ID END, CASE WHEN NOT @cat3 IS NULL THEN cat3ID END, CASE WHEN NOT @cat4 IS NULL THEN cat4ID END, CASE WHEN NOT @cat5 IS NULL THEN cat5ID END, CASE WHEN NOT @cat6 IS NULL THEN cat6ID END, CASE WHEN NOT @cat7 IS NULL THEN cat7ID END, CASE WHEN NOT @cat8 IS NULL THEN cat8ID END, CASE WHEN NOT @cat9 IS NULL THEN cat9ID END, CASE WHEN NOT cat10 IS NULL THEN cat10ID END, CASE WHEN NOT @search IS NULL THEN item END, CASE WHEN NOT @search IS NULL THEN [desc] END
Now that bits sorted, I can work on the main SELECT to actually list the items, and if the price range is passed in to the stored procedure, to filter the results within the price range 🙂
-
another way to go
DECLARE @t TABLE (price MONEY)
INSERT @t
SELECT 5 UNION ALL
SELECT 3 UNION ALL
SELECT 50 UNION ALL
SELECT 52 UNION ALL
SELECT 155 UNION ALL
SELECT 156 UNION ALL
SELECT 495 UNION ALL
SELECT 500 UNION ALL
SELECT 505 UNION ALL
select 1002 union all
select 2001
-- check data again
select * from @t
-- work
select count(np) as RangeCount,
Range= case when np= (0+50)/2 then 'price between 0 and 50'
when np= (51+100)/2 then 'price between 51 and 100'
when np= (500+101)/2 then 'price between 101 and 500'
when np= (501+1000)/2 then 'between 501 and 1000'
else '> 1000'
end
from
(
select np = case when price between 0 and 50 then (0+50)/2
when price between 51 and 100 then (51+100)/2
when price between 101 and 500 then (500+101)/2
when price between 501 and 1000 then (501+1000)/2
else 1001
end
from @t
) w
group by np
-
I must be missing something, what is wrong with this?:
SELECT SUM(Case When R1.price between 0 and 50 Then 1 Else 0 End) as [£0 to £50]
, SUM(Case When R1.price between 51 and 100 Then 1 Else 0 End) as [£51 to £100]
, SUM(Case When R1.price between 101 and 500 Then 1 Else 0 End) as [£101 to £500]
, SUM(Case When R1.price between 501 and 1000 Then 1 Else 0 End) as [£501 to £1000]
, SUM(Case When R1.price > 1000 Then 1 Else 0 End) as [£1000+]
FROM dbo.item AS R1
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Did anybody of you guys hear about relational databases?
Something about placing business rules into data, not into code?
What if tomorrow some ranges will need to be changed?
Can you customers do it?
_____________
Code for TallyGenerator -
Sergiy (6/16/2008)
Did anybody of you guys hear about relational databases?Something about placing business rules into data, not into code?
What if tomorrow some ranges will need to be changed?
Can you customers do it?
I have gone with the joined table suggestion. It has placed the rules in the data, and I can create a backend to change the ranges. It is also a fast solution.
-
I have made a small addition to the price range table that is used in my join. I have manually created a range order field that serves 2 purposes. The first thing it does is to allow the list of counts to be ordered, and then it also gives each range an id to be passed back into the stored procedure to filter the list of items.
I have used this to filter the results:
FROM item INNER JOIN tblPriceRanges ON RangeOrder = @priceRange AND price BETWEEN RangeFrom AND RangeToI have had to create 2 versions, one with the inner join, and one without. If price range id is NULL, which is when the filter is not in place, the select without the inner join is used. If the price range id isn't NULL then the inner join is used. Is there a better way to do this?
Steve
-
Still haven't answered my question.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
rbarryyoung (6/16/2008)
I must be missing something, what is wrong with this?:SELECT SUM(Case When R1.price between 0 and 50 Then 1 Else 0 End) as [£0 to £50]
, SUM(Case When R1.price between 51 and 100 Then 1 Else 0 End) as [£51 to £100]
, SUM(Case When R1.price between 101 and 500 Then 1 Else 0 End) as [£101 to £500]
, SUM(Case When R1.price between 501 and 1000 Then 1 Else 0 End) as [£501 to £1000]
, SUM(Case When R1.price > 1000 Then 1 Else 0 End) as [£1000+]
FROM dbo.item AS R1
Ok, so its a coded solution, a nice and compact coded solution, but how would I then pass the selected price range into my select for filtering the item table?
-
OK, fair enough, I can see that is was not caught up to the changes that you were making.
To answer your question then: from a performance standpoint, 2 different version is preferred. If you want to consolidate the logic however, you can also apply your IS NULL technique to the join conditions:
FROM item
INNER JOIN tblPriceRanges
ON (@priceRange IS NULL)
OR (RangeOrder = @priceRange AND price BETWEEN RangeFrom AND RangeTo)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply