[thelist] SQL: COUNTing and SUMming and whatnot

[Chris W. Parker]

> -----Original Message-----
> From: rudy [mailto:r937 at interlog.com]
> Sent: Tuesday, October 15, 2002 1:46 PM
> To: thelist at lists.evolt.org
> Subject: Re: [thelist] SQL: COUNTing and SUMming and whatnot
>
>
> hi chris

howdy!

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)

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

using the info i gave above, i came up with this query.

SELECT 'occurrences by product: ',
      PP.ppName,
      COUNT(productTracking.ptProduct) AS numberofoccurrences
    FROM PossibleProducts PP
  LEFT OUTER
    JOIN ptProduct.productTracking
      ON PP.id = ptProduct.productTracking"

but i have a feeling that's all wrong. for one, it didn't work (gave me
an error that said "syntax error in join operation") and two, i wasn't
sure what you meant by "occurences.o".


i hadn't tried the others yet.


thanks so far!

chris.