Regarding creating a new variable in SQL-CodePudding

I have a SQL table (temp2) like this:

I want to calculate the balance*rate/sum(balance) for each cat

So, my desired output would be something like this:

To get this output, I used following code:

DROP TABLE IF EXISTS temp3;
create table temp3 as select cat, balance * rate /sum(balance) as prod from temp2
group by cat 

select temp2.emp_id, temp2.cat,temp2.balance, temp2.rate , temp3.prod from temp2 
left outer join temp3 on temp2.cat=temp3.cat

So here I have created a new table to get the answer. Will there be an easier way to get the same results?

CodePudding user response：

There's no need for the new table unless you need to refer to it in multiple queries. You can just join with a subquery.

SELECT t2.emp_id, t2.cat, t2.balance, t2.rate, t3.prod
FROM temp2 AS t2
JOIN (
    SELECT cat, balance * rate /sum(balance) AS prod 
    FROM temp2
    GROUP BY cat
) AS t3 ON t2.cat = t3.cat

There's no need to use LEFT JOIN. Since the subquery gets cat from the same table, there can't be any non-matching rows.

Sometimes it's useful to create the new table so you can add an index for performance reasons.