List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:August 10 1999 3:46pm
Subject:Re: sum
View as plain text  
On Mi, 1999-08-11 01:25:20 +1000, Michael Farr wrote:
> Hi, I dont understand why this query gives me 'Total Owe' = -500.  It seems 
> to have ignored the sum(pay.credit) part of the equation.  Is there are way 
> I could fix it so it would say 'Total Owe' = 132?
> 
> SELECT CONCAT('$ ', (TO_DAYS(NOW()) -
> TO_DAYS(p.treatmentCommenceDate))*p.dailyPayment + p.downPayment) As 'Total Debits To
> Date',
>        CONCAT('$ ', sum(pay.credit)) as 'Total Credit',
>        CONCAT('$ ', (TO_DAYS(NOW()) -
> TO_DAYS(p.treatmentCommenceDate))*p.dailyPayment - sum(pay.credit)) AS 'Total Owe',
>                     (TO_DAYS(NOW()) -
> TO_DAYS(p.treatmentCommenceDate))*p.dailyPayment as a,
>                                                                                      
>    sum(pay.credit) as b,
>        CONCAT('$ ',p.downPayment) as 'Down Payment'
> FROM  Payment pay, Patient p
> WHERE p.patientId = pay.patientId
>   AND p.patientId = 1
> GROUP BY 'Date';
> 
> +----------------------+--------------+-----------+--------+--------+--------------+
> | Total Debits To Date | Total Credit | Total Owe | a      | b      | Down Payment |
> +----------------------+--------------+-----------+--------+--------+--------------+
> | $ 932.00             | $ 500.00     | $ -500.00 | 632.00 | 500.00 | $ 300.00     |
> +----------------------+--------------+-----------+--------+--------+--------------+
> 1 row in set (0.00 sec)

This probably is some wierd bug ...

One comment first:
  GROUP BY must be followed by a field name without apostrophes, not a
  string!  And I hope you're knowing well what you're doing, if you
  select on fields which are not part of the GROUP BY clause?
                    
Do you get the same numbers, if you simplify the query a little bit
concentrating on the main bits?

SELECT (TO_DAYS(NOW()) - TO_DAYS(p.treatmentCommenceDate))*p.dailyPayment                 
 AS a,
                                                                           
SUM(pay.credit) AS b,
       (TO_DAYS(NOW()) - TO_DAYS(p.treatmentCommenceDate))*p.dailyPayment -
SUM(pay.credit) AS diff
FROM  Payment pay, Patient p
WHERE p.patientId = pay.patientId
  AND p.patientId = 1
GROUP BY Date;

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
sumMichael Farr10 Aug
  • Re: sumMartin Ramsch10 Aug
Re: sumMichael Farr17 Aug