Help with Count Distribution

Database
N/A

Help with Count Distribution

I need some help. Within a mailing table there are tracking numbers which can have multiple event time stamps. I am able to get the last date/time event with the MAX function using a SQL statement similar to below:

Sel MAX(eventdatetime) as LastEvent, trackingnumber
from tracking
group by trackingnumber

Each date/time event which occurs also has a zipcode of where the event took place. I want to get the zipcode of where the last date/time event took place in order to get a distribution by zip code. Since the trackingnumber repeats itself in the table I am using, when I add the zipcode to the statement, I get the max date/time for each trackingnumber with a given zipcode.

Sel MAX(eventdatetime) as LastEvent, trackingnumber, zipcode
from tracking
group by trackingnumber, zipcode

Can you offer a suggestion as to how I can get a count/distribution of the zipcodes of where the last event took place?
4 REPLIES
N/A

Re: Help with Count Distribution

select zip_code, count(*)
from
(
Sel zipcode, trackingnumber, eventdatetime
from tracking
-- last event of each trackingnumber
qualify row_number()
over (partition by trackingnumber order by eventdatetime desc)
) as dt
group by 1

Dieter
N/A

Re: Help with Count Distribution

Dieter, thanks.
I'm using Teradata SQL Assistant 7.2. When I try to execute the statement, I receive error "3707: Syntax error, expected something like an 'IN' keyword between ')' and ')'." I will continue to search. Thanks for the help.
N/A

Re: Help with Count Distribution

Oops, i forgot "=1", this should work now:

select zip_code, count(*)
from
(
Sel zipcode, trackingnumber, eventdatetime
from tracking
-- last event of each trackingnumber
qualify row_number()
over (partition by trackingnumber order by eventdatetime desc) = 1
) as dt
group by 1

Btw, the release of SQL Assistant (SQLA 7.2 is quite old) is not important, but the Teradata release, as newer releases got enhanced SQL syntax.

Dieter
N/A

Re: Help with Count Distribution

Thanks.