MySQL SELECT SUM based on another table

Question

I have an table on MySQL

tb_currencies

currencyid | currency_name
CU0001     | IDR
CU0002     | SGD
CU0003     | USD

tb_currency_converters

currencyconverterid | currency_month | from_currencyid_fk | to_currencyid_fk | amount
CC0001              | 2018-03-01     | CU0001             | CU0002           | 0.00009
CC0002              | 2018-03-01     | CU0002             | CU0001           | 10425
CC0003              | 2018-03-01     | CU0003             | CU0002           | 1.31964

tb_budgets

budgetid       | budget_month | departmentid_fk | currencyid_fk
BU201803000001 | 2018-03-01   | DP0003          | CU0002
BU201803000002 | 2018-03-01   | DP0002          | CU0002

tb_items

itemid | item_name | currencyid_fk | price
IT0001 | Mouse     | CU0001        | 165000
IT0002 | Keyboard  | CU0002        | 20
IT0003 | TV LCD    | CU0003        | 350

tb_pro_request

requestid      | budgetid_fk    | itemid_fk | quantity
RQ201803000001 | BU201803000001 | IT0002    | 1
RQ201803000002 | BU201803000001 | IT0003    | 5
RQ201803000003 | BU201803000001 | IT0004    | 1

Example:

My departmentid_fk is: DP0003, means I have Budget with Currency SGD.

On tb_pro_request, there are 2 items transaction different currency(IDR & USD) with Budget Department Currency(SGD).

What I want is, that 2 items with different currency need to convert first to SGD (Due to my Department Budget is: SGD) then SUM it.

*Logic, if the currency is SGD then no need to convert, but if not SGD then convert it first using tb_currency_converters then SUM it.

Is it possible to do in directly to query?

My query code so far:

SELECT
    R.requestid,
    R.budgetid_fk,
    R.category,
    R.itemid_fk,
    SUM(R.quantity),
    R.request_date,
    R.investmentid_fk,
    R.remarks,
    R.approval_status,
    I.itemid,
    I.item_name,
    I.price,
    SUM(R.quantity) * I.price as total, 
    B.budgetid,
    B.budget_month
FROM
    tb_pro_request R,
    tb_items I,
    tb_budgets B
WHERE
    R.itemid_fk = I.itemid AND
    R.budgetid_fk = B.budgetid AND
    R.investmentid_fk = '' AND
    B.active = 'Y' AND
    (R.approval_status = 'P' OR R.approval_status = 'A') AND
    DATE_FORMAT(B.budget_month,'%Y-%m') = '2018-03' AND
    B.departmentid_fk = 'DP0003'
    GROUP BY I.currencyid_fk

You can see there are 3 items with different currency below. What I want, convert to SGD for currency IDR, USD. then SUM it(to be 1 row)

Your question is hard to follow, because I can't see the expected output (a picture is worth a thousand words for SQL questions here). You may want to also pare down your sample data, and only show the columns relevant directly to your query. — Tim Biegeleisen
– Tim Biegeleisen, Commented Mar 5, 2018 at 6:28
Hi @TimBiegeleisen, please have a look on this fiddle: sqlfiddle.com/#!9/82da57/2 and I have updated my question picture description what I want — HiDayurie Dave
– HiDayurie Dave, Commented Mar 5, 2018 at 6:43
@ThorstenKettner Please have a look on Blag answer. It almost fix my issue. Now need to sum all the price after convert to SGD currency — HiDayurie Dave
– HiDayurie Dave, Commented Mar 5, 2018 at 8:08

Blag · Accepted Answer · 2018-03-05 10:06:05Z

1

I think I mess up a bit in your FK, but at least you have the spirit ;)

SQL Fiddle

MySQL 5.6 Schema Setup:

Query 1:

SELECT
    R.budgetid_fk,
    SUM(R.quantity),
    SUM(R.quantity * I.price * COALESCE(CC.amount,1)) as total, 
    B.budgetid,
    B.budget_month
FROM tb_pro_request R 
INNER JOIN tb_items I 
  ON R.itemid_fk = I.itemid
INNER JOIN tb_budgets B 
  ON R.budgetid_fk = B.budgetid 
  AND B.active = 'Y'
LEFT JOIN tb_currency_converters CC 
  ON CC.from_currencyid_fk = I.currencyid_fk 
  AND CC.to_currencyid_fk = B.currencyid_fk
WHERE
    R.investmentid_fk = '' 
    AND (
      R.approval_status = 'P' 
      OR R.approval_status = 'A'
    ) 
    AND DATE_FORMAT(B.budget_month,'%Y-%m') = '2018-03' 
    AND B.departmentid_fk = 'DP0003'
GROUP BY R.budgetid_fk

Results:

|    budgetid_fk | SUM(R.quantity) |             total |       budgetid | budget_month |
|----------------|-----------------|-------------------|----------------|--------------|
| BU201803000001 |               7 | 575.2840143424692 | BU201803000001 |   2018-03-01 |

edited Mar 5, 2018 at 10:06

answered Mar 5, 2018 at 7:06

Blag

5,8942 gold badges26 silver badges48 bronze badges

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

10 Comments

HiDayurie Dave Over a year ago

Hi @Blag, sorry for missing. For Total, formula need to get the currency amount on table tb_currency_converters. Example for requestid: RQ201803000001, price is IDR 149000 then on Total it should be convert to SGD Currency based on table tb_currency_converters. So 149000 * 0.00009

Blag Over a year ago

@HiDayurieDave better like that ? (I switched the to/from FK that was the wrong way)

HiDayurie Dave Over a year ago

OK I checked it and it's working, last question after We convert it I want to sum it for all 13.410000203293748 + 20 + 461.8740141391754 to be 1 row. Is it possible? Because I need to get the result for that price.

Blag Over a year ago

@HiDayurieDave answer updated, that's what you want ?

HiDayurie Dave Over a year ago

Cheers Sir ... Working perfect

|

Thorsten Kettner · Accepted Answer · 2018-03-05 09:10:10Z

1

Your query is invalid SQL, because you are aggregating the data and still show unaggregated columns, such as the requestid. Which requestid? There can be several.

The following groups by budget currency. If department_fk plus budget_month are unique in the table tb_budgets, then we are selecting just a single one and can hence remove the GROUP BY clause.

select
  sum(i.price * r.quantity * coaleasce(cc.amount, 1)) as total,
  c.currency_name
from tb_budgets b 
join tb_pro_request r on  r.budgetid_fk = b.budgetid 
                      and r.approval_status in ('P','A')
                      and r.investmentid_fk = ''
join tb_items i on i.itemid = r.itemid_fk
join tb_currencies c on c.currencyid = b.currencyid_fk
left join tb_currency_converters cc on  cc.currency_month     = b.budget_month
                                    and cc.from_currencyid_fk = i.currencyid_fk
                                    and cc.to_currencyid_fk   = b.currencyid_fk
where b.departmentid_fk = 'DP0003'
and date_format(b.budget_month,'%Y-%m') = '2018-03'
and b.active = 'Y'
group by c.currency_name;

edited Mar 5, 2018 at 9:10

answered Mar 5, 2018 at 8:23

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

3 Comments

HiDayurie Dave Over a year ago

I got error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= itemid join c.currency c on c.currencyid = b.currencyid_fk left join tb_curren' at line 8

Thorsten Kettner Over a year ago

Oops, I started with USING clauses and switched to ON and made a mess. I've corrected the typo.

HiDayurie Dave Over a year ago

Still have an error: SELECT command denied to user 'user_9_82da57'@'100.96.8.14' for table 'currency'

Collectives™ on Stack Overflow

MySQL SELECT SUM based on another table

2 Answers 2

10 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related