Medium Price

[gatarossi]

Dear all,

I have a table with this information:

code quantity unit_value
10 100 0,5
10 150 1,0
10 200 1,5

and I'm trying to do a consult to obtain this:

code quantity unit_value
10 450 1,1111


Now I have this SQL instruction...

SELECT table1.item_code, Avg(DSum([quantity],"table1")) AS Expr1,
Avg((DSum([unit_value]*[quantity],"table1"))/
DSum([quantity],"table1")) AS Expr2
FROM table1
GROUP BY table1.item_code;

But I don´t known if it's correct because the result in this consult
is 1. Maybe I need that the consult return more decimals in this
number (I don't known how can I do it!!!) to verify better the
result...

All my number's fields in my table are decimals, scale:04, precision:
18, format: geral number.

Is it correct? My consult, the results...

Thanks a lot!!!

André.

 

[Wolfgang Kais]

Hello André.

Dear all,

I have a table with this information:

code quantity unit_value
10 100 0,5
10 150 1,0
10 200 1,5

and I'm trying to do a consult to obtain this:

code quantity unit_value
10 450 1,1111


Now I have this SQL instruction...

SELECT table1.item_code, Avg(DSum([quantity],"table1")) AS Expr1,
Avg((DSum([unit_value]*[quantity],"table1"))/
DSum([quantity],"table1")) AS Expr2
FROM table1
GROUP BY table1.item_code;

But I don´t known if it's correct because the result in this consult
is 1. Maybe I need that the consult return more decimals in this
number (I don't known how can I do it!!!) to verify better the
result...

All my number's fields in my table are decimals, scale:04, precision:
18, format: geral number.

Is it correct? My consult, the results...

Thanks a lot!!!

Have you read my reply to your previous post (September 1st)?

 

[gatarossi]

Hello Wolfgang,

Yes!!! But It's doens't work... I think I didn't understand it very
well. If I put totals or not...

And in this SQL instruction, the result is wrong.

Thanks a lot

André.

 

[Wolfgang Kais]

Hello Wolfgang,

Yes!!! But It's doens't work... I think I didn't understand it very
well. If I put totals or not...

And in this SQL instruction, the result is wrong.

My sql statement produces exactly the one row you wanted to see.
100 + 150 + 200 = 450
and
(100*0.5 + 150*1.0 + 200*1.5) / (100 + 150 + 200) =
= (50 + 150 + 300) / 450 = 500/450 = 1.11111.....

So what's wrong with it? Try again:
SELECT code,
Sum(quantity) AS s,
Sum(quantity*unit_value)/Sum(quantity) AS unitVal
FROM [NameOfYourTable] GROUP BY code;

 

[gatarossi]

Hello Wolfgang,

Thanks a lot for your help!!! It's works!!!!


André.