SQL rewriting help

[dikob]

Hi all i'm new here and just wanted to share if anyone could have any thoughts on my delima (glad that i found this site )

i have these tables below:

table Aff:
Aff_ID | SiteName
----------------------
001 ------> yahoo
002 ------> msn
003 ------> espn
... so on

table Affcamps:
Aff_C | Aff_ID | Aff_Type_ID
001 ---> 002 ------> 2
222 ---> 007 ------> 3
333 ---> 001 ------> 2
002 ---> 007 ------> 2
... so on

table AffAgg:
Aff_ID | ecount | signcount | Concount | clickcount
003 --------> 200 ------> 53 ------> 56 ------> 89
001 --------> 647 ------> 64 ------> 66 ------> 87
003 --------> 54 ------> 14 ------> 15 ------> 24
002 --------> 34 ------> 5 ------> 4 ------> 3
003 --------> 12 ------> 2 ------> 1 ------> 5
... so on

now i was advise to sum the following
per Aff_ID w/ Aff_Type_ID = 2:

1. sum(ecount) = ?
2. sum(signcount) = ?
3. sum(Concount) = ?
4. sum(clickvount = ?

my querry goes like this:
SELECT af.SiteName,
aa.Aff_ID,
aa.Ecount,
ab.Scount,
ac.Ccount,
ad.Kcount,
ae.Aff_Type_ID
FROM
Aff af
INNER JOIN
(SELECT Aff_ID, sum(ecount) AS Ecount
FROM AffAgg
GROUP BY Aff_ID) aa
ON
af.Aff_ID= aa.Aff_ID
INNER JOIN
(SELECT Aff_ID, sum(signcount) AS Scount
FROM AffAgg
GROUP BY Aff_ID) ab
ON
af.Aff_ID = ab.Aff_ID
INNER JOIN
(SELECT Aff_ID, sum(Concount) AS Ccount
FROM AffAgg
GROUP BY Aff_ID) ac
ON
af.Aff_ID = ac.Aff_ID
INNER JOIN
(SELECT Aff_ID, sum(clickcount) AS kcount
FROM AffAgg
GROUP BY Aff_ID) ad
ON
af.Aff_ID = ad.Aff_ID
INNER JOIN
(SELECT DISTINCT Aff_ID, Aff_Type_ID
FROM Affcamps where Aff_Type_ID=2) ae
ON
af.Aff_ID = ae.Aff_ID
ORDER BY aa.Aff_ID

i'm sorry if my text is long but i just wanted to show the details of my request. we got 3+ mil rec. on our database thats why i need to omptimize my querry as fine as possible. my querry above runs ok but the DBA advised me to shorten my sql. i have 4 inner joins made because most of the fields i wanted to look for the sum is not indexed. i think only Aff_ID is indexed. Is there anyaway i could shorten my SQL syntax with at least the same quickness as my above querry.

thanks, your help is very much appreciated

regards,

dennis

[aussie girl]

What happens if you used

SELECT SiteName, SUM(ecount),SUM(signcount),SUM(Concount),SUM(clickcount)
FROM Aff, AFFAgg, Affcamps
WHERE Aff.Aff_ID = AffAgg.Aff_ID
AND Aff.Aff_ID = Affcamps.Aff_ID
AND Aff_Type_ID = 2
GROUP BY AffAgg.Aff_ID

[dikob]

hi aussie girl,

i think the SUM sql function doesn't work that way, i got error when it tried it. but thanks for your reply.

err:
Column not found in GROUP
BY clause: SiteName in SELECT or HAVING clause.

thnaks

[chazzy]

hi

dba shouldn't tell you to shorten your sql, you should tell dba to put in better indexes.

as for the query, aussie_girl was almost right... wrong group by item. i don't think you'll need to group by the id, but as the parser was complaining, you will need to group by sitename.