[thelist] SQL: COUNTing and SUMming and whatnot

rudy r937 at interlog.com
Tue Oct 15 18:41:01 CDT 2002

try this

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

my apologies for having omitted the GROUP BY in my previous post

the dot notation is always   [tablenameoralias].[columname]

you had it backwards a few places

your record count query is therefore

select 'records by table: '
     , 'productTracking', count(*)
  from productTracking
union all
select 'records by table: '
     , 'PossibleProducts'
     , count(*)
  from PossibleProducts
union all
select 'records by table: '
     , 'PossibleAdverts'
     , count(*)
  from PossibleAdverts

your 3rd requirement, "the name of each item in #1" sounds like you wanted
product name, and that's part ad parcel of the GROUP BY query


More information about the thelist mailing list