I have separate queries that calculate counts based on grouping of the same
field for each query. I need to output all the grouping/counts in a table
style format. My thought is to generate lists for each query than merge all
the lists into a single query structure (Im using Nate W. ListsToQuery.cfm
for this.) The problem I have is not all the lists are the same length so I
have no way to make sure the output matches the correct group. Here is a
slimmed down version of my queries.
<CFQUERY NAME="source" DATASOURCE="blah">
SELECT source,COUNT(source) AS source_count,COUNT(match) AS match_count
FROM table
WHERE shipped BETWEEN #datefrom# AND #dateto#
GROUP BY source ORDER BY source
</CFQUERY>
<CFQUERY NAME="CN" DATASOURCE="blah">
SELECT source,COUNT(C_TYPE) AS CN_COUNT
FROM table
WHERE shipped BETWEEN #datefrom# AND #dateto# AND C_TYPE = 'CN'
GROUP BY source ORDER BY source
</CFQUERY>
<CFQUERY NAME="C20" DATASOURCE="blah">
SELECT source,COUNT(C_TYPE) AS C20_COUNT
FROM table
WHERE shipped BETWEEN #datefrom# AND #dateto# AND C_TYPE = '20'
GROUP BY source ORDER BY source
</CFQUERY>
I want the output to have 5 columns:
SOURCE - SOURCE_COUNT - MATCH_COUNT - CN_COUNT - C20_COUNT
The first query (source) results are fine because they will always show
every group and the count, if there is no count it will give me a blank
which is fine. The problem I have now is the other queries have additional
criteria (C_TYPE = blah) so if a group does not match that criteria I do not
get the results from the query. I need every group listed and blank or 0
for the group that does not match the criteria. How the heck do I do
that??????. I tried using a sub query but end up with the same results.
There must be some way to do this. I also tried using a UNION but it ends
up duplicating or triplicating each group based on how many queries there
are in the UINION and I found no way around that.
Desperate for a solution here.........
-Adrian
> -----Original Message-----
> From: Dave Watts [mailto:[EMAIL PROTECTED]]
>
>
> If you need a single total for say, all entries in the "match" field, you
> won't want to retrieve it within the same query that gets all the
> entries in
> the "source" field. Whenever you mix actual columns with aggregate
> functions, the functions generate results for each unique entry in the
> actual columns. If you really want to know "how many total"
> rather than "how
> many per source", you'll want to use a separate query for that.
> You could do
> it in one query, but it might actually be less efficient than doing it in
> separate queries.
>
> As for retrieving separate counts for specific values in the "cn" field,
> that's when you'd want to match actual columns with aggregate functions:
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
