Hi
I have a query that I can't solve.
I have a table with these columns (all text apart from contact_id):
contact_id
language_group
town
region
state
country
There are 4 language_groups and each town has varying numbers each
language_group (inc some with zero)
I have to be able to show a table (or spreadsheet) with headers as follows
town| region| state| country| count of language_1|count of language_2|count of
language_3|count of language_4|
I use this query to get the records
<cfquery name="get_demographics_town_lang_group_data"
datasource="#Application.DSN#">
SELECT Count(tbl_demographics.contact_id) AS CountOfcontact_id,
tbl_demographics.language_group, tbl_demographics.town, tbl_demographics.region
FROM tbl_demographics
GROUP BY tbl_demographics.town, tbl_demographics.region,
tbl_demographics.language_group
ORDER BY tbl_demographics.town, tbl_demographics.language_group
</cfquery>
And this code:
<table>
<cfoutput query="get_demographics_town_lang_group_data" group="town">
<tr>
<td>#town# </td> <td>#region#</td>
<cfoutput group="language_group">
<td>#language_group# :: #CountOfcontact_id#</td>
</cfoutput>
</tr>
</cfoutput>
</table>
This works BUT does not get each language-group count into it's own column.
If there are zero records for count of language_1 then count of language_2
moves into count of language_1's column etc.
I've been stuck on this for some time and am really desperate now. Any help
gratefully appreciated. Hope it all makes sense
Seamus
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324236
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
