then i try:
select accountid, count(distinct contactid) from activities where completeddate like '2013-02%' group by accountid;
but this just seems to go into an a very long report, so far its been running for about 6 hours, i would have thought that since it can count the number of distinct contactid's then it would have been straightforward to introduce an additional count?
Can anyone suggest a more economical way to run this type of query?
Not sure how increasing the temp_table_size will help? Is completedate a date data type or a string? If it is a date, then I would suggest changing the query to look at completedate >= '2013-02-01' AND completedate < '2013-03-01'
If you want to speed up the overall processing you will need to create an index on completedate, accountid and contactid. Then it will use only the index to return the valid values without having to go back to the original table data. This makes processing far more efficient.