is there a way in SQL to at first select records from a table and aggregate it and then insert it into another table? For example, this is the given table.
[account_id] | [job_role_id] | [salary]
1 1 10,000
1 2 10,000
1 3 10,000
1 1 20,000
1 2 10,000
2 3 10,000
2 1 20,000
2 2 20,000
2 3 10,000
I need to aggregate the records in such a way that all the salary for same job_role_id given by account_id is added in the following way:
[account_id] | [job_role_id] | [salary]
1 1 30,000
1 2 20,000
1 3 20,000
2 1 20,000
2 2 20,000
2 3 20,000
Excuse my bad English as I am very sleep deprived at this moment and English is my second language.
Yes, you could do that first by coming up with your select statement, you want to aggregate the salary based on the account_id and job_role_code. You would do that using the following:
SELECT account_id, job_role_id, Sum(salary) as salary
FROM job_table
GROUP BY account_id, job_role_id;
The next step is that you want to insert that into some other table to hold this data permanently. We can modify the select like this:
INSERT INTO job_salary_aggregate_table (account_id, job_role_id, salary)
SELECT account_id, job_role_id, Sum(salary) as salary
FROM job_table
GROUP BY account_id, job_role_id;
This would only be effective to run once. If you need to run it again you would need to truncate the data from the job_salary_aggregate_table before attempting to perform the SELECT INTO... again.
As requested, additional count:
INSERT INTO job_salary_aggregate_table (account_id, job_role_id, salary, num_agged)
SELECT account_id, job_role_id, Sum(salary) as salary, Count(1) as num_agged
FROM job_table
GROUP BY account_id, job_role_id;