Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2005
    Posts
    165

    Unanswered: making a statistics table....

    hi. i'm trying to make a report in asp that sums up a whole bunch of statistics. the report is quite lengthy and takes about 2 minutes to load. i thought i would solve that problem by making a table to dump the statistics into instead of calculating the statistics every time the report is viewed. this way, the page would just read the table with the statistics already put in it. these statistics would be updated every day or two so i don't need anything up to the minute.

    however, i have run into a small problem which is actually updating the information in the table. my new table is called OfficeReport and it looks like this:
    Code:
    UserID  Stat1  Stat2  Stat3  ...... Stat32
    23      0      0     0              0
    56      0      0     0              0
    72      0      0     0              0
    this is a query for one of the statistics i am currently using:
    Code:
    SELECT DefendantCase.UserID, COUNT(DefendantCase.UserID) AS CountOfUserID 
    FROM DefendantCase LEFT JOIN UserDescription ON DefendantCase.UserID=UserDescription.UserID 
    WHERE UserDescription.Status=1 AND UserDescription.UserType=1
    GROUP BY DefendantCase.UserID
    if i run this query, this is what i get:
    Code:
    UserID  CountOfUserID
    54      21
    60      10
    52      29
    4       4
    27      22
    how can i modify this query so its output updates the same UserID column in the OfficeReport table? Thanks!
    Last edited by bla4free; 01-31-07 at 17:22.

  2. #2
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    I'm guessing you want to update when the userid is already present in the table and insert when it's not...
    Code:
    -- Insert
    -- OUTER JOIN with OfficeReport WHERE UserID IS NULL should give all
    -- UserID's that are not in OfficeReport
    INSERT OfficeReport (UserID, Stat1)
    SELECT dc.UserID
          ,COUNT(dc.UserID) 
    FROM DefendantCase dc
       LEFT OUTER JOIN UserDescription ud ON dc.UserID = ud.UserID
       LEFT OUTER JOIN OfficeReport  orep ON orep.UserID = dc.Userid
    WHERE ud.Status = 1
    AND   orep.UserID IS NULL
    
    -- Update
    UPDATE orep
    SET Stat1 = COUNT(dc.UserID)
    FROM OfficeReport orep
       INNER JOIN DefendantCase        dc ON dc.UserID = orep.UserID
       LEFT OUTER JOIN UserDescription ud ON dc.UserID= ud.UserID
    WHERE ud.status = 1

  3. #3
    Join Date
    Jan 2005
    Posts
    165
    if i use your update method, i get this error:
    Code:
    An aggregate may not appear in the set list of an UPDATE statement.
    thanks!

  4. #4
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Aaah, yes, an aggregate (COUNT in this case) skips NULL columns.

    Sorry, I seem to have forgotten the GROUP BY in the INSERT en UPDATE this should be added to both:
    Code:
    GROUP BY dc.UserID

Posting Permissions

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