Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
9
Erster Beitrag:
vor 8 Jahren, 4 Monaten
Letzter Beitrag:
vor 8 Jahren, 4 Monaten
Beteiligte Autoren:
Piet van Zanten, Arie, Louis Verbraak, issah

[WD] Query with total

Startbeitrag von Piet van Zanten am 26.05.2009 19:52

Hi All,

I'm starting to use predifined queries that return a total (sum). I've created some, but tey seem not to work.
I'm marking an item with the "Sum" button. The name changes, e.g. to sum_Amount.
However after executing the query I'm not able to get the total with MyQuery.sum_Amount.
It only returns the amount of one of the query records, not the total.
Should I use another way to get the total? I cannot find anything in the help file about using resulting total(s) in code.

Best regards,
Piet

Antworten:

Hello Piet,

Use GROUP BY in combination.

HTH.

von Louis Verbraak - am 26.05.2009 20:34
Thanks Louis,

I was expecting to get a quick and clean way of getting a total by using the query wizard, but no go. I thought the sum could work on a single file query too, but looking at the examples I don't think it will. I'm not gonna spend any more time on this as I can easily calculate the sum in a FOR ALL loop.

Regards,
Piet

von Piet van Zanten - am 27.05.2009 08:07
Did you do an HReadFirst on the query before accessing the sum value?

issah

von issah - am 27.05.2009 13:45
Hi Issah,

Yes I did and if I run the query test, then the result shows e.g 8 lines. All the "totals" columns show the same value as the item value, except for one which shows an amount that is neither a total nor a single value.


Regards,
Piet

von Piet van Zanten - am 27.05.2009 15:41
Piet,
if you look at your query code, you will see WD adds a GROUP BY on all other columns you SELECT (datumvan, datumtot, perscode, minuten)
So you will get a SUM per datumvan+datumtot+perscode+minuten
That's exactly what yout screenshot show us. It should work that way.

So, which SUM do you need? Per date? Or per perscode?

AFAIK you can not do this with the query-gui. You need to write the query-code yourself.

Arie

von Arie - am 27.05.2009 15:52
Hi Arie,

You are right, I want a total for PersCode, so I have set all other fields to not display.
Now it works :spos:
Thanks for your help everyone.

Regards,
Piet

von Piet van Zanten - am 27.05.2009 15:57
That's right. I presume you use the others fields (via parameters) to filter your data.

The query code finally would look something like:
SELECT perscode, SUM(minuten) FROM yourtable WHERE datum BETWEEN param1 AND param2.

Arie

von Arie - am 27.05.2009 16:05
Hi Arie,

I edited my previous post just before your answer.
Thanks again,
Regards,
Piet

von Piet van Zanten - am 27.05.2009 16: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.