[thelist] SQL: COUNTing and SUMming and whatnot

[Joshua Olson]

----- Original Message -----
From: "Chris W. Parker" <cparker at swatgear.com>
Sent: Tuesday, October 15, 2002 5:42 PM


> let me list the tables and their fields.
>
> productTracking:
>  * id (autonumber)
>  * ptProduct (number, comes from id.PossibleProducts)
>  * ptReferralSource (number, comes from id.PossibleAdverts)
>
> PossibleProducts:
>  * id (autonumber)
>  * ppName (text, name of product)
>
> PossibleAdverts:
>  * id (autonumber)
>  * paName (text, name of product)

So far, the names of the tables and the descriptions of what they do are
somewhat confusing, since the three tables you said you have are:

1. stores the individual occurrences
2. stores the possible ads (productA, productB, productC, etc.)
3. stores the possible sources (magazineA, magazineB, magazineC, etc.)

I'm not sure how those three tables match with the schema you presented
above.  But, here's the basic concept of what will work:

SELECT
  (SELECT Count(*) FROM productTracking WHERE ptProduct =
PossibleProducts.id) AS product_count,
  ppName AS product_name,
  (SELECT Count(*) FROM PossibleAdverts) AS total_records
FROM PossibleProducts

It's not particularly optimized (it really isn't at all) but it will get the
job done.  Given the small recordset and the fact that you are running off
of Access (which implies a website that is not hit heavily) this should be
fine.

Again, I'm not sure how the tables relate to what you are looking for
exactly, but the above query should at least give some general guidance.

FWIW, the total records subquery query is completely redundant and should be
a separate query.  Or, you could do a union:

SELECT
  'Total Records' AS product_name,
  Count(*) FROM PossibleAdverts) AS product_count
UNION ALL
SELECT
  ppName AS product_name,
  (SELECT Count(*) FROM productTracking WHERE ptProduct =
PossibleProducts.id) AS product_count
FROM PossibleProducts

Then, use the first record of the result set to populate the "total records"
column of the display.

-joshua