mysql - 在MySQL中将两个数字相乘

我用的是phpMyAdmin，我有两张桌子：
___营业税

|--------|----------|------------|
| STX_Id | STX_Name | STX_Amount |
|--------|----------|------------|
|      1 |    Tax 1 |       5.00 |
|      2 |    Tax 2 |      13.50 |
|--------|----------|------------|

___计费数据

|--------|---------------|------------|------------|--------------|----------|---------------------|
| BIL_Id | BIL_BookingId | BIL_Date   | BIL_Status | BIL_Quantity | BIL_Rate | BIL_ApplicableTaxes |
|--------|---------------|------------|------------|--------------|----------|---------------------|
|      1 |             2 | 2018-03-06 | notcharged |           2  |   100.00 |                   1 |
|      2 |             2 | 2018-03-07 | notcharged |           3  |   105.00 |                 1,2 |
|--------|---------------|------------|------------|--------------|----------|---------------------|

我想每天在___BillableDatas中列出一个可计费物品的列表，这取决于物品的状态（已计费与未计费）。
所以像这样：

|------------|-----------------|--------------------|------------------|--------------------|
|    Date    | BIL_Sum_Charged | BIL_Sum_Notcharged | Taxes_ForCharged | TaxesForNotCharged |
|------------|-----------------|--------------------|------------------|--------------------|
| 2018-03-06 |            0.00 |             200.00 |             0.00 |              10.00 |
| 2018-03-07 |            0.00 |             315.00 |             0.00 |             58.275 |
|------------|-----------------|--------------------|------------------|--------------------|

我实际拥有的是：

SELECT b.BIL_Date,
SUM(case when b.BIL_Status = "charged" then b.BIL_Rate else 0 end
 / (1 + LENGTH(b.BIL_ApplicableTaxes)
        - LENGTH( REPLACE ( b.BIL_ApplicableTaxes, ",", "") ) ))
   as BIL_Sum_Charged,
 SUM(case when b.BIL_Status = "notcharged" then b.BIL_Rate else 0 end
 / (1 + LENGTH(b.BIL_ApplicableTaxes)
        - LENGTH( REPLACE ( b.BIL_ApplicableTaxes, ",", "") )))
      as BIL_Sum_Notcharged,
  SUM(case when b.BIL_status = "charged" then s.STX_Amount else 0 end)
      as STX_TAX_Charged,
  SUM(case when b.BIL_status = "notcharged" then s.STX_Amount else 0 end)
     as STX_TAX_NotCharged
 FROM ___BillableDatas b
 INNER JOIN ___SalesTaxes s
 ON FIND_IN_SET(s.STX_id, b.BIL_ApplicableTaxes) > 0
 WHERE b.BIL_HotelId='cus_CNHLMiMOzP5cuM'
 AND b.BIL_BookingId='2'
 GROUP BY b.BIL_Date
 ORDER BY b.BIL_Date ASC

这个查询的问题是我没有正确的BIL_Sum_Charged和BIL_Sum_NotCharged的和，因为和没有乘以BIL_Quantity列。
例如，我已经105而我应该315为BIL_Sum_Notcharged为2018-03-07。最后的税金也一样。
请问我的错误在哪里？
请参见SQL小提琴：
http://sqlfiddle.com/#!9/8b3cb/1
非常感谢你的帮助。

最佳答案

经过讨论，这似乎是你需要的。希望这有帮助。

SELECT b.BIL_Date,
SUM(case when b.BIL_Status = "charged" then b.BIL_Rate else 0 end
 / (1 + LENGTH(b.BIL_ApplicableTaxes)
        - LENGTH( REPLACE ( b.BIL_ApplicableTaxes, ",", "") ) )) * b.BIL_Quantity
   as BIL_Sum_Charged,
 SUM(case when b.BIL_Status = "notcharged" then b.BIL_Rate else 0 end
 / (1 + LENGTH(b.BIL_ApplicableTaxes)
        - LENGTH( REPLACE ( b.BIL_ApplicableTaxes, ",", "") ))) * b.BIL_Quantity
      as BIL_Sum_Notcharged,
  SUM(case when b.BIL_status = "charged" then s.STX_Amount else 0 end) * (SUM(case when b.BIL_Status = "charged" then b.BIL_Rate else 0 end
 / (1 + LENGTH(b.BIL_ApplicableTaxes)
        - LENGTH( REPLACE ( b.BIL_ApplicableTaxes, ",", "") ) )) * b.BIL_Quantity) / 100
      as STX_TAX_Charged,
  SUM(case when b.BIL_status = "notcharged" then s.STX_Amount else 0 end) * ( SUM(case when b.BIL_Status = "notcharged" then b.BIL_Rate else 0 end
 / (1 + LENGTH(b.BIL_ApplicableTaxes)
        - LENGTH( REPLACE ( b.BIL_ApplicableTaxes, ",", "") ))) * b.BIL_Quantity) / 100
     as STX_TAX_NotCharged
 FROM ___BillableDatas b
 INNER JOIN ___SalesTaxes s
 ON FIND_IN_SET(s.STX_id, b.BIL_ApplicableTaxes) > 0
 WHERE b.BIL_HotelId='cus_CNHLMiMOzP5cuM'
 AND b.BIL_BookingId='2'
 GROUP BY b.BIL_Date
 ORDER BY b.BIL_Date ASC;

根据讨论编辑。