mysql - 求和不适用于乘法

Question

SELECT    Sum((pvc.cpt_amount) * (pvc.unit)) AS billed_amount, 
          pvc.cpt_amount, 
          pvc.unit 
FROM      payment            AS pay 
LEFT JOIN patient_insurances AS pi 
ON        pay.who_paid = pi.id 
LEFT JOIN patient_visit_cpt AS pvc 
ON        ( 
                    pay.encounter_id = pvc.id 
          AND       pay.claim_id = pvc.claim_id ) 
LEFT JOIN patient_visit AS pv 
ON        pvc.visit_id = pv.id 
WHERE 
and       pi.insurance_id = 761 
AND       pay.created_at <= '2016-04-01 23:00:00' 
AND       pay.created_at >= '2016-03-31 00:00:00' 
GROUP BY  pay.check_number, 
          pv.attending_provider_id

for unit = 1 and cpt_amount = 145, but output comes like mulitplied with 4. like 1 * 145 = 580.. please any one give solution

Answer 1

您的 Group By 可能有误。

同时删除 WHERE 子句之后的关键字 AND

试试下面的查询

SELECT Sum((pvc.cpt_amount) * (pvc.unit)) AS billed_amount, 
       pvc.cpt_amount,pvc.unit 
FROM payment AS pay 
LEFT JOIN patient_insurances AS pi  ON pay.who_paid = pi.id 
LEFT JOIN patient_visit_cpt AS pvc ON(pay.encounter_id = pvc.id AND pay.claim_id = pvc.claim_id) 
LEFT JOIN patient_visit AS pv ON pvc.visit_id = pv.id 
WHERE pi.insurance_id = 761 AND pay.created_at <= '2016-04-01 23:00:00' 
      AND pay.created_at >= '2016-03-31 00:00:00' 
GROUP BY  pvc.cpt_amount,pvc.unit