Counting vs. summing

[Alfredomulet]

Hello everyone, I'm having this discussion with a fellow data modeler regarding the use of COUNT vs. SUM. I'm used to build the DAILY_TRANSACTIONS_FACT with a unique TXN_ID column whose values are just consecutive numbers. When calculating number of transactions, I just do COUNT(TXN_ID).

My colleage on the other hand, build them with a TXN_COUNT, rather than the ID, whose value is allways 1. Then, the calculation is SUM(TXN_COUNT).

What are the advantages disadvantages of each approach??

Thanks!
alfredo

 

[entaroadun]

Assuming that TXN_ID is in your index, and that TXN_COUNT is not, the COUNT can be calculated from the index only, while the SUM needs to load all of the individual rows. COUNT should therefore be faster.

Many ROLAP reporting tools have arcane rules around COUNT functions, and will generate SQL that will seem unpredictable to novice report writers. In that case, I would prefer SUM, and find some way to index to speed the operation.

 

[Alfredomulet]

Thanks entaroadun, i haven't look at it that way, it makes perfectly sense. By the way we're using BO, is it one of the tools you're talking about?
cheers,
alfredo

 

[entaroadun]

I'm not familiar with the BO SQL engine, but I think it does have issues. Just ask one of your more junior report developers / power users if count measures are difficult to get right.