CODEWITHSUNDEEP
mysqlsqlsqlite
student_R123
student_R123

Reputation: 1002

Regarding creating a new variable in SQL

I have a SQL table (temp2) like this:

enter image description here

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

So, my desired output would be something like this:

enter image description here

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?

Upvotes: 0

Views: 41

Answers (2)

Shawn
Shawn

Reputation: 52549

You actually don't need a join or subquery at all thanks to window functions:

SELECT emp_id, cat, balance, rate,
       balance * rate / sum(balance) OVER (PARTITION BY cat) AS prod
FROM temp2
ORDER BY emp_id;

gives

emp_id  cat  balance  rate  prod              
------  ---  -------  ----  ------------------
1       1    1000.0   0.25  0.0625            
2       3    1250.0   0.25  0.0568181818181818
3       2    1500.0   0.25  0.0681818181818182
4       1    1000.0   0.25  0.0625            
5       2    1250.0   0.25  0.0568181818181818
6       3    1500.0   0.25  0.0681818181818182
100     1    1000.0   0.25  0.0625            
101     3    1250.0   0.25  0.0568181818181818
102     2    1500.0   0.25  0.0681818181818182
103     1    1000.0   0.25  0.0625            
104     2    1250.0   0.25  0.0568181818181818
105     3    1500.0   0.25  0.0681818181818182

(Create an index on temp2.cat for best performance).

This is also more accurate; both yours and Barmar's uses balance and rate in the grouped query without including those values in the GROUP BY clause - that's an error in most databases, but Sqlite will pick a random row from the group to use as the values, which when different rows in the group have different values for them, will throw off the final calculations. To do it properly with grouping (If, for example, you're using an old database version that doesn't support window functions), you need something like

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

Upvotes: 1

Barmar
Barmar

Reputation: 782098

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.

Upvotes: 1

Related Questions