|
| Iniciado por guest, 20,mar. 2015 10:18 - 11 respuestas |
| |
| | | |
|
| |
| Publicado el 20,marzo 2015 - 10:18 |
Hi Everyone
I want to sum the price and quantity and divide it, but WD gives an error
SELECT item, sum(price)/sum(quantity)
FROM items_ordered
GROUP BY item
Any ideas on the correct syntax?
Regards
Marius |
| |
| |
| | | |
|
| | |
| |
| Publicado el 20,marzo 2015 - 11:07 |
Marius
In the query generator add a 'calculated item' as price/quantity - give it a name you can recognise.
This will add a row to the selected columns.
Apply Sum to this row.
Should return ..... SELECT SUM(TicketActivity.UnitCost / TicketActivity.UnitQty) AS fCostQty FROM TicketActivity |
| |
| |
| | | |
|
| | |
| |
| Publicado el 20,marzo 2015 - 12:08 |
Thanks Derek.
Your syntax pointed me in the correct direction. I should only use one SUM and not TWO as in your example.
Regards
Marius |
| |
| |
| | | |
|
| | |
| |
Miembro registrado
31 mensajes |
|
| Publicado el 20,marzo 2015 - 12:17 |
What's the error that WinDev tells you? -- that should prove helpful to solving the issue...
... My guess is that you have an Item record with a total quantity of 0 and are getting the divided-by-0 error... just a guess, though.
If you DECODE() the SUM(Quantity) value so that if it's 0 you don't do the division it might solve your problem. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 23,marzo 2015 - 08:03 |
I'm struggling with the issue!
I use the "WDSQL Tool" to test the code.
If I run this query in MS SQL
SELECT item, SUM(price), SUM(quantity), SUM(price)/SUM(quantity)
FROM items_ordered
GROUP BY item;
using the following dataset
Item="Item 1",Quantity=1,price=82.22
Item="Item 1",Quantity=2,price=88.70
I get the following result
Item 1,177.92,3,59.306666 (Which is correct)
As the above sql syntax doesn't work in WD, I now use
SELECT item, SUM(price), SUM(quantity), SUM(price/quantity)
FROM items_ordered
GROUP BY item;
but it yield the incorrect result
Item 1,177.92,3,133.57
I can see what is happening here. The query performs the calculation part for each row,
then add the calculations up at the end. It should however add it all up and then
at the end do the calculation part.
Any ideas?
Regards
Marius |
| |
| |
| | | |
|
| | |
| |
| Publicado el 23,marzo 2015 - 09:25 |
Marius,
try this
SELECT item, sum_price, sum_quantity, sum_price / sum_quantity FROM
(
SELECT item, SUM(price) as sum_price, SUM(quantity) as sum_quantity
FROM items_ordered
GROUP BY item
) |
| |
| |
| | | |
|
| | |
| |
| Publicado el 23,marzo 2015 - 10:07 |
Hi Arie
Spot on. Thanks!!!!
Regards
Marius |
| |
| |
| | | |
|
| | |
| |
| Publicado el 24,marzo 2015 - 13:38 |
Hi Marius,
although Aries code pointed you to the right direction, there is a little problem.
Depending on your backend database, what will happen if sum_quantity=0 ?
You will have to use CASE (which is SQL-92 compatible) or just get the sums of the price and quantity from the SQL query and do the price/quantity in WD code ...
Steven Sitas |
| |
| |
| | | |
|
| | |
| |
| Publicado el 24,marzo 2015 - 13:45 |
Hi Steven,
A division by 0 in HFSQL will not produce an error but will return 0. (We wished we had such an option in WinDev because this is the behavior we want 99% of the time, but unfortunately such an option does not exist.)
Best regards,
Alexandre Leclerc
Edit 1: Typo correction. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 24,marzo 2015 - 16:21 |
Hi Alexandre,
I didn't know that. It would be nice if we had it in WD language also ..
But is this an ANSI thing ? Do you how SQL Server or Oracle handles this (?)
Steven Sitas |
| |
| |
| | | |
|
| | |
| |
| Publicado el 25,marzo 2015 - 05:48 |
Thanks Steven
Noted.
Regards
Marius |
| |
| |
| | | |
|
| | |
| |
| Publicado el 25,marzo 2015 - 13:11 |
Hi Steven,
I'm not an ANSI or SQL-92 expert. I made a quick search and found out that many databases have different ways of handling it. Some offer an option to activate or deactivate ANSI warnings and will result in different behaviors. I think that ANSI behavior is to raise an error, but I cannot be certain about that. Some other will return a null value.
Best regards,
Alexandre Leclerc |
| |
| |
| | | |
|
| | | | |
| | |
|