MySQL SELECT SUM 基于另一个表(另一种情况) [英] MySQL SELECT SUM based on another table (Another Case)

问题描述

继续我之前的最后一个问题.但现在我在下面添加了一些条件:

Continue my last question before. But now I add some condition below:

根据工作查询代码:

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 = 'DP0002'
GROUP BY R.budgetid_fk;

在该代码上，它将从某个列中获取价格的总和.

On that code, It will get the total SUM of price from some column.

requestid      | budgetid_fk    | category  | itemid_fk | quantity | currencyid_fk | price | discount | userid_fk
RQ201803000001 | BU201803000002 | Item      | IT0001    |          |       |
RQ201803000002 | BU201803000002 | Project   |           | 20       | CU0002 | 750 | 10 | US0004

现在我需要添加额外的代码来计算类别项目(RQ201803000002)

Now I need to add extra code to calculate Category Project(RQ201803000002)

价格与

SUM(R.quantity * I.price * COALESCE(CC.amount,1)) as total

逻辑:

(quantity * (price * currency)) - discount as total2

*need to convert the currency first
*get department from userid_fk

然后积累起来

total + total2

您可以在此处

推荐答案

我想你只是想要这个(如果 discount 来自另一个表格?):

I think you just want this (apart if the discount come form another table ?):

SUM(R.quantity * (I.price - discount) * COALESCE(CC.amount,1)) as total

这篇关于MySQL SELECT SUM 基于另一个表(另一种情况)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！