Обсуждение: Money in numeric field

I have a question involving money data stored in a numeric(9,2) field,
and posible errors with there manipulation.

in short, the table has these columns:

store: int
amount: int2
cost: numeric(9,2)

What I need to find is the total amount of money spent in a particular
store, so I do something like the second query:

SELECT count(*) from material where store = 9;
 count
-------
   360
(1 fila)

SELECT sum(cost*amount) from material where store = 9;
   sum
----------
 48244.35
(1 fila)


Is it posible to have errors after multipling the numeric value by and
int and then adding them all with the SUM() function?

If the answer is yes, where do the errors acumulate, and how can I solve it?

P.D.: I'm using PG and PHP in the middle.

--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador

Martín Marqués wrote:
> I have a question involving money data stored in a numeric(9,2) field,
> and posible errors with there manipulation.
> 
> in short, the table has these columns:
> 
> store: int
> amount: int2
> cost: numeric(9,2)
> 
> What I need to find is the total amount of money spent in a particular
> store, so I do something like the second query:
> 
> SELECT count(*) from material where store = 9;
>  count
> -------
>    360
> (1 fila)
> 
> SELECT sum(cost*amount) from material where store = 9;
>    sum
> ----------
>  48244.35
> (1 fila)
> 
> 
> Is it posible to have errors after multipling the numeric value by and
> int and then adding them all with the SUM() function?

Not really.
"numeric" represents numbers exactly up to 131072 digits before
the decimal point and up to 16383 digits after the decimal point.

Yours,
Laurenz Albe

El día 20 de abril de 2012 05:51, Albe Laurenz
<laurenz.albe@wien.gv.at> escribió:
> Martín Marqués wrote:
>> I have a question involving money data stored in a numeric(9,2) field,
>> and posible errors with there manipulation.
>>
>> in short, the table has these columns:
>>
>> store: int
>> amount: int2
>> cost: numeric(9,2)
>>
>> What I need to find is the total amount of money spent in a particular
>> store, so I do something like the second query:
>>
>> SELECT count(*) from material where store = 9;
>>  count
>> -------
>>    360
>> (1 fila)
>>
>> SELECT sum(cost*amount) from material where store = 9;
>>    sum
>> ----------
>>  48244.35
>> (1 fila)
>>
>>
>> Is it posible to have errors after multipling the numeric value by and
>> int and then adding them all with the SUM() function?
>
> Not really.
> "numeric" represents numbers exactly up to 131072 digits before
> the decimal point and up to 16383 digits after the decimal point.

Yes, I read that it's stored in a PACKED DECIMAL or BCD, but I was
worried abouit the SUM() function.

For what I read so far, I guess I won't have any problem, but just
wanted to be sure.

--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador