Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
11
Erster Beitrag:
vor 2 Jahren, 11 Monaten
Letzter Beitrag:
vor 2 Jahren, 11 Monaten
Beteiligte Autoren:
Marius, Alexandre Leclerc, Steven Sitas, Arie, DerekT

[WD18] - Sql syntax

Startbeitrag von Marius am 20.03.2015 09: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

Antworten:

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


von DerekT - am 20.03.2015 10:07
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

von Marius - am 20.03.2015 11:08
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

von Marius - am 23.03.2015 07:03
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
)

von Arie - am 23.03.2015 08:25
Hi Arie

Spot on. Thanks!!!!

Regards
Marius

von Marius - am 23.03.2015 09:07
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

von Steven Sitas - am 24.03.2015 12:38
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.

von Alexandre Leclerc - am 24.03.2015 12:45
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

von Steven Sitas - am 24.03.2015 15:21
Thanks Steven

Noted.

Regards
Marius

von Marius - am 25.03.2015 04:48
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

von Alexandre Leclerc - am 25.03.2015 12:11
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.