[WD] Query with total

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



Edited 1 time(s). Last edit at 05/26/2009 09:52PM by Piet van Zanten.

Hello Piet,

Use GROUP BY in combination.

HTH.

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

Did you do an HReadFirst on the query before accessing the sum value?

issah

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

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

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



Edited 1 time(s). Last edit at 05/27/2009 06:02PM by Piet van Zanten.

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

Hi Arie,

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