?
Solved

Product Price Range Breakdown

Posted on 2007-10-08
6
Medium Priority
?
923 Views
Last Modified: 2013-12-24
Hello,
I am getting stuck on an advanced (to me at least) query. I would like to output the following:

Browse By Price Range:
$0-$25 (5)
$25+$50 (12), etc.

Now I could easily make a seperate query for each price range, but would like to accomplish this is one cfquery if possible.

So basicly I would have to find out all of the product within a specific range and display the recordcount in ().

Thank you in advance.

Coldfusion MX 7 and MySQL

Product (Database Table)
Product_ID, Product_Price

--
Frank
0
Comment
Question by:guyute91
  • 3
  • 2
6 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 20034930
select price_range,count(*) from
(select case when product_price >= 0 and product_price < 25 then '0-25'
                   when product_price >= 25 and product_price < 50 then '25-50'
                    else 'over 50'
           end as price_range from product)
group by price_range
0
 
LVL 52

Expert Comment

by:_agx_
ID: 20035032
sdstuber's suggestion is a good one assuming "Product" will always have at least one row of data.  Just add an alias:

            ...
            end as price_range from product) AS someAliasHere
        group by price_range

If you have a lot of price ranges, or they change frequently,  another option is to store the price ranges in a table, then do a join


SELECT  pr.MinPrice, pr.MaxPrice, COUNT(p.Product_Id)
FROM    PriceRange AS pr
      LEFT JOIN Product AS p
         ON p.Product_Price BETWEEN pr.MinPrice AND pr.MaxPrice
GROUP BY pr.MinPrice, pr.MaxPrice
0
 

Author Comment

by:guyute91
ID: 20035528
Thanks for the responses.

Using sdstuber's solution I get the following error:
Every derived table must have its own alias

I apologize if this is something easy, but do not know enough about advanced SQL.

--
Frank
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Accepted Solution

by:
sdstuber earned 400 total points
ID: 20035576
select price_range,count(*) from
(select case when product_price >= 0 and product_price < 25 then '0-25'
                   when product_price >= 25 and product_price < 50 then '25-50'
                    else 'over 50'
           end as price_range from product) as  price_summaries
group by price_rangeback to top
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 100 total points
ID: 20035588
> Thanks for the responses.
> Using sdstuber's solution I get the following error:
> Every derived table must have its own alias

That's the small correction I mentioned in my original response.  You need to add an alias to the derived table

select price_range,count(*) from
(select case when product_price >= 0 and product_price < 25 then '0-25'
                   when product_price >= 25 and product_price < 50 then '25-50'
                    else 'over 50'
            end as price_range from product) AS someAliasHere
        group by price_range
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20035616
yes the problem was the "derived table" refers to query I put inside parentheses.
I didn't give it a name  agx added "as someAliasHere".

In my follow up I made it "as price_summaries"

basically MySql wants you to always query from a named source.  
looks like I have a cut-n-paste error in my follow up above, so here is my query again
with the fix

select price_range,count(*) from
(select case when product_price >= 0 and product_price < 25 then '0-25'
                   when product_price >= 25 and product_price < 50 then '25-50'
                    else 'over 50'
           end as price_range from product) as  price_summaries
group by price_range



0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question