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 Count

    FROM 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 RangeTo

    I 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