[thelist] SQL: COUNTing and SUMming and whatnot

[rudy]

try this

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

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


rudy