[thelist] SQL: COUNTing and SUMming and whatnot
rudy
r937 at interlog.com
Tue Oct 15 15:47:01 CDT 2002
hi chris
>i've got three tables...
>
>1. stores the individual occurrences
>2. stores the possible ads (productA, productB, productC, etc.)
>3. stores the possible sources (magazineA, magazineB, magazineC, etc.)
you can do your report in one query if you don't mind lots of unions
folding the third one in might not make sense (see below)
>1. a count total for each item in #2.
> (i.e. how many times was productB recorded?)
select 'occurrences by product: '
, a.productname
, count(o.occurrenceid) as numberofoccurrences
from ads a
left outer
join occurrences.o
on a.productid = o.productid
the left outer join allows some products to have no occurrences, and still
show up on this report with a 0 for number of occurrences
> 2. a count total for all records in the db.
> (i.e. there are 500 records in our database.)
select 'records by table: '
, 'occurrences'
, count(*)
from occurrences
union all
select 'records by table: '
, 'products'
, count(*)
from products
union all
select 'records by table: '
, 'sources'
, count(*)
from sources
counts rows in each table
> 3. the name of each item in #1.
select 'this occurrence is called: '
, o.name
, 0
from occurrences o
not sure how useful this will be, or whether you want detail occurrences
mixed with totals, but if you do, that's what the placeholders in the
select list are for, you can union this query with the other two
see
Result set row count along with query results
http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_ci
d480948_tax285649,00.html
> i'd ultimately like to be able to
> create bar graphs based on
> percentages of product_count/total_records.
isaac wrote a neat article about this --
Basic CF: Nesting CFOUTPUTs and graphing results
http://evolt.org/article/graphing/17/22043/index.html
rudy
More information about the thelist
mailing list