Just a guess, but it looks like you might be overthinking your aggregate
and perhaps backing up and looking at it from a little further away
might help you see where you're running into an issue here.
Now this code here is already inside of 2 cfoutput groups against the
get_Sales query - one for market and a second for manager (and I would
suggest using the unique id for the manager as your group column instead
of "manager" -- it may not come up, but it could cause a problem if
there coincidentally turn out to be a pair of "John Smith" managers at
the company later on, so grouping on the unique identifier will prevent
that from happening).
See below for more
> <cfoutput>
> <cfquery datasource="#request.dsn#" name="get_leads">
> Select Count(*) AS total
> From Leads
> WHERE r_agn = #get_Sales.usr_id#
> </cfquery>
> #get_Sales.Fname# #get_Sales.usr_lname# :: #get_leads.total#<br>
> <br>
> </cfoutput>TotalLeads<br>
> <cfset sumTotal = 0>
> <cfloop query="get_leads">
> <cfoutput>
> <CFSET sumTotal = #sumTotal# + #get_leads.total#>
> #sumTotal#</cfoutput>
> </cfloop>
> </cfoutput>
So one of the first things I notice is that you've got the ungrouped
cfoutput around the cfquery here, which means that you'll be fetching
the get_leads query and looping over it potentially multiple times per
manager. Is usr_id the unique id for the manager or is usr_id an agent
who belongs to that manager? I'm guessing based on the placement of your
ungrouped output that usr_id is an agent who belongs to the manager. If
that's the case, then you want to move your placement of the aggregation
code inside of that ungrouped output like this:
<!--- start a new aggregate --->
<cfset sumTotal = 0>
<cfoutput>
<cfquery name="get_leads">...</cfquery>
<cfset sumTotal = sumTotal + get_leads.total>
</cfoutput>
What's probably happening currently (again, based on the guess that
usr_id is an agent who belongs to the current manager, and so there are
multiple agents per manager) is that you're looping over the agents and
fetching the leads for each agent, but because you're then exiting the
ungrouped cfouptut, it's just overwriting your get_leads query on each
iteration of the output. By the time you loop over get_leads, it has
only one row, not one row for each agent, and so the result is only
showing leads for the last agent and that's all you're getting in your
aggregate.
Lastly, if at all possible, I would recommend not using a get_leads
query here, and instead use a sub-query in your get_Sales query. That
will be much more scalable, adding only a fes miliseconds to the
get_sales query as a whole, whereas using the separate query in your
output here will typically add a roughly the similar number of
miliseconds _per_ get_leads query. Occasionally it becomes difficult to
avoid this sort of thing tho...
alternatively, if you're unable to move the lead count into the
get_sales query, you might consider this instead:
<cfquery name="get_leads" ...>
select r_agn, count(*) as total
from leads
where r_agn IN (#valuelist(get_sales.usr_id)#)
</cfquery>
<cfquery name="get_Sales" dbtype="query">
select get_sales.*, get_leads.total as leadcount
from get_sales
left join get_leads on
(get_leads.r_agn = get_sales.usr_id)
</cfquery>
<cfoutput query="get_sales" group="market">
<cfoutput group="manager">...</cfoutput></cfoutput>
This would reduce the number of requests to your database to just the 2
- one for the get_sales query and another for the get_leads query -- the
second get_sales query in my example here (called a "query of query" in
case you haven't seen them yet) will merge the data from both queries.
hth,
ike
--
s. isaac dealey ^ new epoch
isn't it time for a change?
ph: 503.236.3691
http://onTap.riaforge.org/blog
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:295937
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
