Count distinct values within the same table

[#1]

I am loading a table (Y_Sent) that has numerous attributes. Including: 'SDSID', 'Filename' and 'Direction'. With multiple SDS IDs appearing in the table, I'd like to include a count of Filenames against each SDS ID as part of my load script.

Could somebody kindly assist?

Thanks

JW

[swuehl · Accepted solution]

Maybe like

Y_Sent:

LOAD

     SDSID,

     Filename,

     Direction

FROM ....;

LEFT JOIN (Y_Sent) LOAD

                 SDSID,

                 count(distinct Filename) as FileCount

resident Y_Sent

                  group by SDSID;

[sushil353]

Try this:

first of all get the count of each file using groupby function. and after that do a inner join with the main table.

load

count(filenames)

from main_table

groupby SDS ID

inner join main_table

load * from main_table

Please explain your issue a bit more..

HTH

Sushil