Query using both aggregate sum and count

D

dlawner

Using a three table join, I am trying to get an aggregate sum of one
field from one table and the count for a field in another table in the
same recordset. The problem is that if multple records are returned
from the join, the aggregates are multiplied by the number of records
returned. The query that I was trying to use is the following:

SELECT s.showID, s.Headline Headline, s.Status, s.LastEditedDate
LastEditedDate, s.AffiliateNo, s.LastEditedBy LastEditedBy,
u.FirstName, u.LastName, count(c.clipid) NumClips, sum(p.SizeMB) SizeMB

FROM VISShow s WITH (READPAST), VISVideoProxy p with
(index(inx_VISVideoProxy_showid), READPAST), VISClip c
(index(inx_visclip_affiliateno),READPAST), AffiliateUser u (READPAST)
WHERE c.showid =* s.showid
AND s.ShowID = p.ShowID
AND c.status in('L', 'D')
AND s.LastEditedBy = u.UserNo
AND c.AffiliateNo = 5
And s.AffiliateNo = 5
AND s.Status = 'L'
GROUP BY s.showID, s.Headline, s.Status, s.LastEditedDate,
s.LastEditedBy, s.AffiliateNo, u.FirstName, u.LastName

I tried the following which works if I didn't need an outer join for
the num clips in the second join

SELECT x.showID, x.SizeMB, y.NumClips
FROM
(
SELECT s.showID, sum(p.SizeMB) as SizeMB
FROM VISShow s WITH (READPAST), VISVideoProxy p with
(index(inx_VISVideoProxy_showid), READPAST), AffiliateUser u
(READPAST)
WHERE s.ShowID = p.ShowID
AND s.LastEditedBy = u.UserNo
And s.AffiliateNo = 5
AND s.Status = 'L'
GROUP BY s.showID
) as x
JOIN
(
SELECT s.showID, count(c.clipid) as NumClips
FROM VISShow s WITH (READPAST), VISClip c
(index(inx_visclip_affiliateno),READPAST)
WHERE c.showid =* s.showid
AND c.status in('L', 'D')
AND c.AffiliateNo = 5
And s.AffiliateNo = 5
AND s.Status = 'L'
GROUP BY s.showID
) as y on (y.showID = x.showID)


Any other ideas short of using a temp table would be appreciated.
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Well, you could use a UNION as a derived table and then sum the
resultset (I've removed the table hints for clarity):

SELECT showID, Sum(SizeMB) As SizeMB, Count(NumClips) As NumClips
FROM
(
SELECT s.showID, p.SizeMB, 0 As NumClips
FROM VISShow s , VISVideoProxy p, AffiliateUser u
WHERE s.ShowID = p.ShowID
AND s.LastEditedBy = u.UserNo
And s.AffiliateNo = 5
AND s.Status = 'L'

UNION ALL

SELECT s.showID, 0 As SizeMb, c.clipid As NumClips
FROM VISShow s , VISClip c
WHERE c.showid =* s.showid
AND c.status in('L', 'D')
AND c.AffiliateNo = 5
And s.AffiliateNo = 5
AND s.Status = 'L'
) As X
GROUP BY ShowID

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9asqIechKqOuFEgEQKeoQCgnwP6dOd5qvNvFWb+CQX9jdJVqgkAoIzO
psmH+x9uX4BaDwkS9PZ4NJ06
=2waY
-----END PGP SIGNATURE-----
 
Top