I have a large and slow database 'LargeSlowTable' which records clicks by users. I only have read permission, not write, so I cannot use Global Temporary Tables. I want to count the number of clicks, grouped by week. I also want to count the distinct/unique number of clicks. The following code does this nicely. (There is a reason I have the second Select nested inside the first Select.)
SELECT Week, COUNT(Click), COUNT(DISTINCT Click) FROM
(
SELECT
Week,
Click
FROM LargeSlowTable
WHERE Id = 2
)
GROUP BY Week
ORDER BY Week;
Now, I also want a count of the total number of unique clicks, from the starting date to the present. This is not simply the sum of all unique clicks per week, as those may shows clicks by the same user. Therefore, I need to run another command which ignores the GROUP BY Week part.
Can I do this without calling the LargeSlowTable again?
Finally, I would also like a sub-total per week of the number of unique clicks, from the starting date to that particular week.
Can I do this without calling the LargeSlowTable again?
