Hi,

I would like to perform a calculation like this:

(Column1 + Column2) * Column3

I have tried to do this using the SUM function with appropriate Grouping,
But some how when the grouping only returns one record I get 2 x the value
expected.

My Query looks like this:
SUM((Column1 + Column2)*Column3)

Column1 = 5
Column2 = 5
Column3 = 1
SumOfExpr. = 20 Should have been 10

Thanks
Petter Topp
Dbisam 4.24

Petter,

<< SumOfExpr. = 20 Should have been 10 >>

I'm getting 10 here.  Is there more than 1 record in the table, or is there
more to the query in terms of joins, etc. that you didn't post ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

Here is the complete sql

SELECT a.*, SUM(a.Sum_Fylke + a.Sum_Rtv),  a.Dato, a.Tid,
b.Rekv_Navn, b.Rekv_Adr, b.Rekv_PostNr, b.Rekv_Poststed, b.Inst_Gruppe_nr,
c.Navn,
d.Org_Navn, d.Pol_Navn,
e.Inst_Gruppe, e.Inst_Adresse, e.Inst_PostNr, e.Inst_PostSted
FROM Resultat a, Setup d
LEFT JOIN Rekv b ON (a.Rekv_Kode = b.Rekv_Kode)
LEFT JOIN Inst_Grupper e ON(b.Inst_Gruppe_Nr = e.Inst_Gruppe_Nr)
LEFT JOIN Analyser c ON (a.Kode = c.Kode)
WHERE (c.Avdeling = :Avdeling) AND  (a.Takst <> NULL AND a.Betaler = '3' )
GROUP BY b.Inst_Gruppe_Nr, a.Rekv_kode , a.Kode

As far as I can remember the SUM function is applied according to the Group
by Clause.
I have not been able to figure out why the result always is the 2 x the
value.
For the moment I have solved this by calculating the value during the
population of the "Resultat" table.

Thanks
Petter Topp


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> skrev i melding
news:3B3801B6-AA69-49AC-99B0-34C684862BA7@news.elevatesoft.com...
> Petter,
>
> << SumOfExpr. = 20 Should have been 10 >>
>
> I'm getting 10 here.  Is there more than 1 record in the table, or is
> there more to the query in terms of joins, etc. that you didn't post ?
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>
>

Petter,

<< As far as I can remember the SUM function is applied according to the
Group by Clause.  I have not been able to figure out why the result always
is the 2 x the value. >>

Joins can cause repeating values, especially when the values are on the left
side of a LOJ.  Hence, you're summing the same value more than once.  You
can see this by running the query without the grouping and summing.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks Tim,

Is it another way to produce a sum of two columns using SQL in a similar way
to calculated filds?

Regards
Petter Topp


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> skrev i melding
news:E67D6A24-4D72-4B11-9397-2CD2753C53E3@news.elevatesoft.com...
> Petter,
>
> << As far as I can remember the SUM function is applied according to the
> Group by Clause.  I have not been able to figure out why the result always
> is the 2 x the value. >>
>
> Joins can cause repeating values, especially when the values are on the
> left side of a LOJ.  Hence, you're summing the same value more than once.
> You can see this by running the query without the grouping and summing.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

First thing I'd try (and I'm fairly new to this!) is sticking a DISTINCT
in after the SELECT.

/Matthew Jones/

Petter,

<< Is it another way to produce a sum of two columns using SQL in a similar
way to calculated filds? >>

Yes, but you'll have to use a script and do the GROUP BY query first into a
temporary table, and then the join query second using the temporary table
from the first query.

--
Tim Young
Elevate Software
www.elevatesoft.com