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
```