How to create a ID_COUNT field?

[btrompetter]

Hello,

I have a table with sales per adress

Now I need a count field wich is 1 for every distinct adress.

I must replace the count (distinct ad_id) with sum (ad_id_count)

How is the peek statement to create this field?

thanks for help

Best wishes

Bastian

[sbaldwin · Accepted solution]

Hi, if you sorted the data as it was loaded by the address field you could use the following to check if the new record was the same as the one before, if it is different then 1 otherwise 0?

if(address = peek(address),0,1) as ad_id_count

Thanks

Steve

[btrompetter]

Hey

thanks for your reply.

But I have to deal with 150 million records.Is there another way to create such field?

What about a sepreate table with only the AD_ID and a count_filed, did this have the same effect? Or Is a count(DISTINCT) faster?

Best wishes

Bastian