The following query returns some data about a forum, which is part of a
super-forum or "conference" :
<cfquery name="getSums" datasource="schoolsforums">
SELECT '#application.districts.name[ctr]#' as distName,
a.id,
a.conferenceidfk,
count(a.name) as forumsNbr,
sum(a.msgcount) AS msgs,
sum(a.viewcount) as views
FROM sf_forums a
WHERE a.conferenceidfk = '#application.districts.id[ctr]#'
group by conferenceidfk
</cfquery>
This works ok. Now I would like to modify the query so that it returns also the
number sum of the threads
of all the forums, from table sf_threads in which the column forumidfk is
related to a.id (the forum.id) :
<cfquery name="getSums" datasource="schoolsforums">
SELECT '#application.districts.name[ctr]#' as distName,
a.id as id,
a.conferenceidfk,
count(a.name) as forumsNbr,
sum(a.msgcount) AS msgs,
sum(a.viewcount) as views,
(select count(*) from sf_threads b where b.forumidfk = a.id) as
threadsCount
FROM sf_forums a, sf_threads b
WHERE a.conferenceidfk = '#application.districts.id[ctr]#'
group by conferenceidfk
</cfquery>
No error message, "threadsCount" is correct but forumsNbr, msgs, views that
were ok in the first query, are now incorrectly much larger.
Please advise the correct sintax.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k
Archive:
http://www.houseoffusion.com/groups/CF-Newbie/message.cfm/messageid:3501
Subscription: http://www.houseoffusion.com/groups/CF-Newbie/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
