Should I use Sum() or Count(DISTINCT)

Wed, 05 Oct 2011 13:36:22 GMT

Hello experts,

According to the QV manuals (and QT's training courses) I should try to avoid using Count(DISTINCT X) and try to add 1 for each row ( e.g. 1 as Counter) in the table and use Sum(Counter). The sum() method is much faster than Count(DISTINCT X).

I'm changing one of my applications to get the application optimized.
The first reports I changed from Count(DISTINCT) to Sum() where considerably faster after the change.
But a lot of the reports are a bit more complicated than just Sum(Counter). I'm frequently using set modifiers to only count data that a specific report needs.

To my surprise using Count(DISTINCT) is often faster than using sum() when the reports get more complicated.
More complicated in this case is, in the report, to use the following as an expression:

If(Aggr(Sum({$ <TypeID={7}>}Head_Counter),OrderNumber) > 1, Sum(Head_Counter)/(Sum({$ <TypeID={7}>}Head_Counter)), (0/1))

Compared to

Count (DISTINCT If(Aggr(Count(DISTINCT InvNo),OrderNumber) > 1, OrderNumber)))

In my application I have around 3 million rows and I have created 2 separate report where one uses Sum() and the other Count(DISTINCT).

I'm aware that the number of rows in the table might change the result but I'm wondering when to use sum() and when to use Count(DISTINCT).

Should the approach be to do both and use whatever method is fastest? This will take more time in total.

Or should I still use Sum() (perhaps it is the logic in my report that can be optimized)?
I.e. If(Aggr(Sum({$ <TypeID={7}>}Head_Counter),OrderNumber) > 1, Sum(Head_Counter)/(Sum({$ <TypeID={7}>}Head_Counter)), (0/1))

Anyone that can enlighten the rest of us or has come accross the same thing and found an effective approcah to this dilemma?

br
Martin

topic Should I use Sum() or Count(DISTINCT) in QlikView

Should I use Sum() or Count(DISTINCT)