Including a "count" filter in query.

[DROFNUD]

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:

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

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:
[pre]SELECT ref,code,model,size,colour FROM products WHERE code='xyz' AND colour='Red';[/pre]

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

[pre]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';​

[/pre]

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...


----------------------------------------

http://www.itspeedway.net

 

[Katerine]

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):

SELECT section, item, ct 
FROM (
[indent]SELECT 'Colour' AS section, colour AS item, count(ref) AS ct[/indent]
[indent]FROM products[/indent]
[indent]WHERE code = 'xyz'[/indent]
[indent]GROUP BY colour[/indent]
[indent]UNION SELECT 'Size' AS section, size AS item, count(ref) AS ct[/indent]
[indent]FROM products[/indent]
[indent]WHERE code = 'xyz'[/indent]
[indent]GROUP BY size[/indent]
) 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.

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

Katie