Counting

[christrinder]

Hello,

OK, this is going to be tricky trying to explain this. My DB contains a table, and one of the fields is named 'sref' (short for section reference). I want to produce a summary page showing the amount of records that contain 1 in the sref field, THEN the number of records that contain 2 in the sref field, THEN the number of records that contain 3 in the sref field, and so on. Short of writing a seperate SQL statement for each count, I don't know how best to go about it. Any ideas?

Chris

[Spudhead]

Not sure - if you've got SQL server, then you can set the query up as a stored procedure that contains the 3 SELECT statements and returns the values via parameters - I'm don't know if this is possible using any other DB setup. Although it doesn't sound like your queries will be that intensive; running those 3 seperately on the same page, utilising the same connection, shouldn't hit performance too hard.

[raf]

“Contain”, you say. Does that mean that you have that value (1, 2, 3) in the field or part of the value (1.1, 1.2, 1.3) ?

If first option,

SELECT DISTINCT sref, Count(*) as [numberinsref] FROM table GROUP BY sref

If second option. You can probably still do it in one statement using a few subquerys, a LIKE and some wildcards, but i’m not sure. Anyway, it wouldn’t be faster then running these few separate querys (as Spudhead said)

Code would then be something like

SELECT DISTINCT sref, Count(*) as [numberinsref] FROM table WHERE sref LIKE ‘1.%’ GROUP BY sref


In this case, it would probably be best to split the variable into two different variables (since searching on numbers goes way faster then searching on text, and your code would get much simpler.)