0

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?

  • Which version of Oracle? – Philᵀᴹ Nov 16 '15 at 13:02
  • You could simply read it as SELECT Week, COUNT(Click), COUNT(DISTINCT Click) FROM LargeSlowTable GROUP BY Week ORDER BY Week; – LBogaardt Nov 16 '15 at 13:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.