INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Including a "count" filter in query.

Including a "count" filter in query.

(OP)
Hi,

I have a table of products that includes column for "Model", "Colour" and "Size" etc. The customer wants to display a filter on the site where you can Tick a Colour and/or size and it displays the amount of records for each.

Something like this:

Products (27)
Colour:
- Red (15)
- Blue (8)
- Yellow (4)
Size:
- XSmall (2)
- Small (5)
- Medium (10)
- Large (8)
- XLarge (2) 

I've seen this kind of thing on several sites and I am looking for the best way to achieve it.

Currently I get the products in a particular group (code) with the following:
SELECT ref,code,model,size,colour FROM products WHERE code='xyz' AND colour='Red'; 

I have been looking at using "SUM(CASE WHEN" as per below:

SELECT 
SUM(CASE WHEN colour='Red' THEN 1 ELSE 0 END) AS "cRed",
SUM(CASE WHEN colour='Green' THEN 1 ELSE 0 END) AS "cGreen",
SUM(CASE WHEN colour='Yellow' THEN 1 ELSE 0 END) AS "cYellow",
SUM(CASE WHEN colour='Black' THEN 1 ELSE 0 END) AS "cBlack",
SUM(CASE WHEN size='Small' THEN 1 ELSE 0 END) AS "sSmall",
SUM(CASE WHEN size='Medium' THEN 1 ELSE 0 END) AS "sMedium",
FROM products WHERE code='xyz';

This SELECT needs to be dynamically created based on the Colours and Sizes in the previous SELECT as the colours are not always definitive.

Is there a better way of achieving this?
Si...

----------------------------------------
www.itspeedway.net

RE: Including a "count" filter in query.

I think you may be overthinking this? Especially since it needs to be dynamic, it seems it would be easier to do something like this (untested):

CODE --> SQL

SELECT section, item, ct 
FROM (
SELECT 'Colour' AS section, colour AS item, count(ref) AS ct
FROM products
WHERE code = 'xyz'
GROUP BY colour
UNION SELECT 'Size' AS section, size AS item, count(ref) AS ct
FROM products
WHERE code = 'xyz'
GROUP BY size
) AS subqry ORDER BY section, item


(this assumes that ref is a unique id)

Then you'd just iterate and display the values, and when section changes, you put in a new header. Again, this is untested, but I think something like this should work. smile

(edited. I made a few obvious mistakes that I noticed right away. Above is the corrected code)

Katie

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close