Select with grouping in MySQL

Question

I have 2 tables :

'pay'

|id_tax|value|id_currency|
--------------------------
|1     |100  |12         |
|1     |200  |12         |
|1     |300  |13         |
|2     |200  |12         |
|2     |200  |12         |
--------------------------

'rate'

|id_currency|rate|
------------------
|12         |1   |
|13         |6,12|
------------------

What SQL query will result in this?

|id_tax|value               |
-----------------------------
|1     |100*1+200*1+300*6,12|
|2     |200*1+200*1         |
-----------------------------

Quassnoi · Accepted Answer · 2011-03-28 17:26:35Z

2

SELECT  id_tax, SUM(value * rate)
FROM    pay p
JOIN    rate r
ON      r.id_currency = p.id_currency
GROUP BY
        id_tax

answered Mar 28, 2011 at 17:26

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Erik · Accepted Answer · 2011-03-28 17:45:40Z

1

In MySQL, you can calculate it using session variables:

SET @s = 0; 

SELECT  id_tax, @s := s +(value * rate)
FROM    pay p
JOIN    rate r
ON      r.id_currency = p.id_currency
GROUP BY
   p.id_tax

or in a pure set-based but less efficient way:

SELECT  pay.id_currency, pay.val, SUM(pay.value * rate.rate)
FROM    pay
JOIN    rate
ON      rate.id_currency = pay.id_currency
GROUP BY
        pay.id_tax

answered Mar 28, 2011 at 17:45

Erik

4462 silver badges5 bronze badges

1 Comment

Quassnoi Over a year ago

He wants plain sums, not cumulative. Also, when copying my answer from here: stackoverflow.com/questions/5462205/… , you could, I don't know, at least change wording, or something.

Collectives™ on Stack Overflow

Select with grouping in MySQL

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related