Pages

Friday, February 13, 2015

Select Count Distinct

The Select statement doesn't have a way to let you do a count of distinct records.

Today I found this neat way to do it anyway, in the method \Data Dictionary\Tables\LedgerJournalTable\Methods\numOfVouchers:
...
sql = 'select count(distinct %1) from %2 where %3 = %4 and %5 = %6 and %7 = %8';

sql = strFmt(sql,
             ReleaseUpdateDB::backendFieldName(tableNum(LedgerJournalTrans), fieldNum(LedgerJournalTrans, Voucher)),
             ReleaseUpdateDB::backendTableName(tableNum(LedgerJournalTrans)),
             ReleaseUpdateDB::backendFieldName(tableNum(LedgerJournalTrans), fieldNum(LedgerJournalTrans, JournalNum)),
             sqlSystem.sqlLiteral(this.JournalNum),
             ReleaseUpdateDB::backendFieldName(tableNum(LedgerJournalTrans), fieldNum(LedgerJournalTrans, DataAreaId)),
             sqlSystem.sqlLiteral(ledgerJournalTrans.DataAreaId),
             ReleaseUpdateDB::backendFieldName(tableNum(LedgerJournalTrans), fieldNum(LedgerJournalTrans, Partition)),
             getcurrentpartitionrecid());
...

3 comments:

Tommy Skaue said...

It seems for the most part, you should get the same execution plan and performance using "GROUP BY", so the following example also work to achieve the same result:

static void CountingUsingGrouping(Args _args)
{
LedgerJournalTrans ledgerJournalTrans;
LedgerJournalId journalNum;
Voucher voucher;

// Some examples
journalNum = 'FJ-000004';
voucher = 'IF-000002';

select count(RecId) from ledgerJournalTrans
group by ledgerJournalTrans.Voucher, ledgerJournalTrans.JournalNum
where ledgerJournalTrans.Voucher == voucher
&& ledgerJournalTrans.JournalNum == journalNum;

info(int642str(ledgerJournalTrans.RecId));
}

Mkz said...

Hi :)

I'm not totally agree...

I mean that:

A
A
B
C
B
count distinct = 3

Select count + group by
A 2
B 2
C 1

Different results.

Tommy Skaue said...

I see what you mean, Manel. I still use GROUP BY when I want to Count, but you can use Count (distinct my_column) to achieve certain "distinct" results, but not so easily from X++. I try to avoid queries in strings because they can blow up at run-time.

More details on aggregation using Count distinct can be found here: http://www.sqlteam.com/article/how-to-use-group-by-with-distinct-aggregates-and-derived-tables