[thelist] SQL: COUNTing and SUMming and whatnot

Chris W. Parker cparker at swatgear.com
Tue Oct 15 15:20:00 CDT 2002


hi.

i'm trying to create a little reporting page for a database that we've
got that is used to track our ads. it's a bit primitive but i'd like to
display the information in as useful a way as i can. (btw, the way it is
used, is our sales people are supposed to fill out a form while they are
talking to someone on the phone. i.e. "where did you hear about this
product?" etc.)

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.)

what i'd like to do is pull back a recordset (i'm using vbscript and
access btw) that gives me...

1. a count total for each item in #2. (i.e. how many times was productB
recorded?)
2. a count total for all records in the db. (i.e. there are 500 records
in our database.)
3. the name of each item in #1.

i don't think this is possible without multiple queries, but i really
don't know much more about sql than the basics. i imagine the recordset
could look like this.

|   product count   |   product name   |  total records  |
+-------------------+------------------+-----------------+
|        15         |   productA       | 500             |
|        5          |   productB       | 500             |
|        34         |   productC       | 500             |
|        52         |   productD       | 500             |
+-------------------+------------------+-----------------+


any way i can do that with one query? if not, please give me suggestions
as to how i can accomplish this. i'd ultimately like to be able to
create bar graphs based on percentages of product_count/total_records.


thanks for your help!
chris.



More information about the thelist mailing list