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:

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:

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:

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!I'm guessing you want to update when the userid is already present in the table and insert when it's not...
-- 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|||if i use your update method, i get this error:
An aggregate may not appear in the set list of an UPDATE statement. thanks!|||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:
GROUP BY dc.UserID
:osql