[thelist] SQL: COUNTing and SUMming and whatnot

[rudy]

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