CODEWITHSUNDEEP
mysqlsql
dshukertjr
dshukertjr

Reputation: 18612

Performing calculation where there are common column values

I want to select values, perform some calculations, let's say multiply two columns, and then add them where one of the column value is the same. For example, if I have a table like the following:

id     prm
1      12
3      14
1      13
2      20
1      17
3      11

I want to first multiply id and prm, then add them wherever there is common id's, so 1st, 3rd, and 5th rows would be added, and 2nd and 6th rows would be added. Then I want the output to be in descending order of id. The final out put would look like:

75 40 42

Because 3*14+3*11=75, 2*20=40, and 1*12+1*13+1*17=42. I hope my question is clear. Is there a way to do this in one sql statement?

Upvotes: 0

Views: 65

Answers (2)

Gayathri
Gayathri

Reputation: 902

select id, sum(id*prm)
from tablename
group by id order by 2 desc

Check this-> http://sqlfiddle.com/#!9/07290/8 if this matches your requirement.

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can just group by id.

select id, sum(id*prm)
from yourtable
group by id

To get the result in a single row, you could do

select 
max(case when id = 1 then `total` end) as id1,
max(case when id = 2 then `total` end) as id2,
max(case when id = 3 then `total` end) as id3
from (
select id, sum(id*prm) as `total`
from yourtable
group by id) t

Upvotes: 0

Related Questions