[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
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
More information about the thelist
mailing list