> I need obtain a few field counts but based on different criteria.
> The following almost works but I need something like this:
>
> (COUNT(cn) ='21') AS C21_COUNT AND
> (COUNT(cn) ='20') AS C20_COUNT
>
> I tried doing this in a UNION but could not get it working.
> Can COUNT be used on a varchar field...?
Yes, it can be used regardless of the datatype of the field. It simply tells
you "how many".
> I also need the totals not based on the grouping, I can do this
> with a separate query no problem, but it seems like I should be
> able to get it our of a single query...
>
> SELECT
>
> source,
> COUNT(*) AS source_count,
> COUNT(match) AS match_fld61,
> COUNT(cn) AS C21_COUNT,
> COUNT(cn) AS C20_COUNT
>
> FROM mytable
>
> WHERE ship_date BETWEEN #form.datefrom# AND #form.dateto#
>
> GROUP BY source
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:
SELECT cn,
COUNT(cn)
FROM mytable
GROUP BY cn
This will get you how many of each "cn".
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
------------------------------------------------------------------------------
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.
