--- In firebird-support@yahoogroups.com, Sudheer Palaparambil wrote:
> Hi,
>
> How to SUM the line_discount on line no.2 ?
>
> SELECT S.bill_no, S.discount, SUM( SD.line_value ),
> SUM( ( SD.line_value * SD.discount_p ) / 100 ) AS line_discount
> FROM sale S
> LEFT JOIN sale_det sd ON ( SD.company_id = S.company_id )
> AND ( SD.parent_id = S.id )
> WHERE ( ( S.company_id = :dCID ) AND ( S.entry_date = :dAsOn ) )
>
> Is this the correct way to sum ? Or should I use SUM for
> individual column like this ?
>
> ( SUM( SD.line_value ) * SUM( SD.discount_p ) ) / 100 AS
> line_discount

That depends on the result you want. Say you have five records, all of
which contained the value 1 in line_value and and 10 in discount_p.
(1*10*5) would give you 50, whereas (1*5)*(10*5) would give you 250.
Most likely you want the former (If I buy 5 items at £1 and get 10%
discount on each, I would expect to pay £4.50, and not get away with
£2.50)

I'm also wondering whether you really want to include sale without any
sale_det, or if you actually want an [INNER] JOIN rather than your
LEFT [OUTER] JOIN.

HTH,
Set