aggregate query problems

[Seamus Campbell-3]

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=17837.14401.4

[Jason Fisher-3]

I think you need an outside table that has just your 4 language_groups
in it, and then LEFT OUTER JOIN to ensure you get at least one record
for each language, with the COUNT()s that can be zero.

SELECT tbl_langs.language_group, Count(tbl_demographics.contact_id) AS CountOfcontact_id, tbl_demographics.town, tbl_demographics.region
FROM tbl_langs LEFT OUTER JOIN
tbl_demographics ON tbl_langs.language_group ON tbl_demographics.language_group
GROUP BY  tbl_demographics.town, tbl_demographics.region, tbl_langs.language_group
ORDER BY tbl_demographics.town, tbl_langs.language_group

That should do it ...

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:324238
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4

[Seamus Campbell-3]

Thanks Jason, but that doesn't seem to help.

I've put a copy up here
http://www.working-traveller.com/g/demographicsold.cfm

to show you what I mean.

Any more clues? and thanks again
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:324245
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4

[Jason Fisher-3]

Seamus,

Can you post your updated query?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:324252
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4

[Seamus Campbell-3]

Hi Jason

updated query here (I'm using access at the moment)

SELECT tbl_member_language_group.language_group_name, Count(*) AS CountOfcontact_id, tbl_demographics.town, tbl_demographics.region
FROM tbl_member_language_group
LEFT  JOIN tbl_demographics
ON tbl_member_language_group.language_group_name = tbl_demographics.language_group
GROUP BY  tbl_demographics.town, tbl_demographics.region, tbl_member_language_group.language_group_name
ORDER BY tbl_demographics.town, tbl_member_language_group.language_group_name

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:324271
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4

[Jason Fisher-3]

OK, I wonder if it's the * in COUNT(*) ... try this:

SELECT tbl_member_language_group.language_group_name,
        COUNT(tbl_demographics.contact_id) AS CountOfcontact_id,
        tbl_demographics.town,
        tbl_demographics.region
FROM tbl_member_language_group LEFT JOIN
        tbl_demographics ON tbl_member_language_group.language_group_name = tbl_demographics.language_group
GROUP BY tbl_demographics.town, tbl_demographics.region, tbl_member_language_group.language_group_name
ORDER BY tbl_demographics.town, tbl_member_language_group.language_group_name

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:324275
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4

[Seamus Campbell-3]

Hi,
thanks - I did try both ways - neither way worked.

Seamus
> OK, I wonder if it's the * in COUNT(*) ... try this:


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:324277
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4

[Jason Fisher-3]

Hmmm ... do you get a record for every language group if you CFDUMP that
query or if you run it directly on the DB server?
 


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:324293
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4

[Seamus Campbell-3]

Ta - just tried that and no records for all language groups.

BUT that did make me realise one of my (many) errors - the database is actually quite complicated so I created a table (tbl_demographics) which I re-populate every time I run this query - and that does not collect towns with zero language groups and I can't see how I could.

I think I'll leave this for a while - it's driving me nuts and wasting far too much time.
thanks very much for your help

Seamus

>Hmmm ... do you get a record for every language group if you CFDUMP that
>query or if you run it directly on the DB server?



>Hmmm ... do you get a record for every language group if you CFDUMP that
>query or if you run it directly on the DB server?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:324298
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4

[Jason Fisher-3]

LOL, I know that feeling.  With that LEFT JOIN, though, you should see
records even where there are none in tbl_demographics ... odd.
 


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:324301
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4