0

What is the most efficient way to stack a field on one table with a summary field summarizing child data in another table?

For example, I have an account table and a task table. Below is the query I'm currently executing to compare a stamped value on the account table to actual counts from the task table.

SELECT
    account.sfid,
    account.lastactivitydate,
    account.sales_activities_last_30_days__c AS expectedcount,
    (
        SELECT COUNT(*)
        FROM salesforce.task
        WHERE task.accountid = account.sfid AND
              task.activitydate >= CURRENT_DATE - INTERVAL '60 days'
    ) AS actualcount
FROM salesforce.account
;

This is a naive attempt to get the data I want, and I'd appreciate any suggestions on optimizing this query to get results like what's shown in the table below.

sfid lastactivitydate expectedcount actualcount
---- ---------------- ------------- -----------
abc  2019-11-30       3             3
def  2019-11-27       5             4
0

Test

SELECT account.sfid,
       account.lastactivitydate,
       account.sales_activities_last_30_days__c AS expectedcount,
       COALESCE(subquery.cnt, 0) AS actualcount
FROM salesforce.account
LEFT JOIN ( SELECT task.accountid, COUNT(*) cnt
            FROM salesforce.task
            WHERE task.activitydate >= CURRENT_DATE - INTERVAL '60 days'
            GROUP BY task.accountid
          ) AS subquery ON subquery.accountid = account.sfid;

or

SELECT account.sfid,
       account.lastactivitydate,
       account.sales_activities_last_30_days__c AS expectedcount,
       COUNT(task.accountid) AS actualcount
FROM salesforce.account
LEFT JOIN salesforce.task 
    ON task.accountid = account.sfid
   AND task.activitydate >= CURRENT_DATE - INTERVAL '60 days'
GROUP BY account.sfid,
         account.lastactivitydate,
         account.sales_activities_last_30_days__c;
| improve this answer | |

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.