www.webdeveloper.com
Results 1 to 4 of 4

Thread: SQL rewriting help

  1. #1
    Join Date
    Aug 2006
    Posts
    2

    SQL rewriting help

    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

  2. #2
    Join Date
    Jun 2006
    Posts
    472
    What happens if you used
    Code:
    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

  3. #3
    Join Date
    Aug 2006
    Posts
    2
    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

  4. #4
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    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.
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles