Problem with COUNT

[ndeeley]

Hi,

I have a table showing a persons skill in any particular application. So there are 4 columns of skills - Access, CF, HTML, SQL and numerous rows of people names and a check if they have that skill.

This is then output to a webpage showing the same checkboxes.

I want to show a count of the number of people who have that skill at the bottom of the column.

I can do this for each skill:

<cfquery datasource="taskbook" name="GetGMC" maxrows="1">
select		count(GeoMedia) as GeoMediaCount
from		tblSkillBase
group by	GeoMedia
</cfquery>

and output the query result at the bottom of the row, but I've have to write 4 queries, which seems really inefficient.

Is there any way to write just one query, that counts the number of checks in a column, and produces a sum for each one?

I'm using ancient Coldfusion MX by the way!
Thanks
Neil

[acoder]

You can add the GeoMedia field and get back counts for each:

select GeoMedia, count(*) as ...

[ndeeley]

Hi acoder,

I have tried that, and grouped the query by all the columns, but CF doesn't return the count for each column:

<cfquery datasource="taskbook" name="GetGMC" maxrows="1">
select		GeoMedia, count (*) as GeoMediaCount,
		Strumap, count (*) as StrumapCount
from		tblSkillBase
group by	GeoMedia, Strumap
</cfquery>

..it just returns a value of 1. Do I need to loop over the query in my results set?

Thanks for your help!
Neil

[acoder]

Right, I assumed that the skills were in one column. So they're in 4 separate columns? In that case, you can use a variation of the latest query, but with 1 count. This will give you counts of those that have each skill alone and those that have a combination and you can tot up the totals.

If that's not the solution, can you give some examples of what each field contains.