Count in teradata

[iceman23]

Hello,

Will you be able to help me in achieving my expected outcome?
Table 1 shows sampler items and the corresponding names, what I would like to achieve is that, i'll be able to count the number of sampler items regardless of the corresponding names (see expected outcome table).

**Note just disregard the single quotes (use for spacing purposes)



Table

sampler ' ' name
sample1 ' ' oyster
sample2 ' ' shrimp
sample3 ' ' pork
sample3 ' ' beef
sample4 ' ' chicken


expected outcome

sampler ' ' name ' ' sampler count
sample1 ' ' oyster ' ' 1
sample2 ' ' shrimp ' ' 1
sample3 ' ' pork ' ' 2
sample4 ' ' chicken ' ' 1

[amitpatel66]

Try this:

 
SELECT x.sampler,(SELECT name from table_name WHERE sampler = x.sampler AND ROWNUM < 2) name, Sampler_Count FROM
(SELECT sampler, COUNT(*) Sampler_Count from table_name GROUP BY sampler) x
 
 

I notice that in the expected output, you want to fetch the name (either of the one) in case there are more than one record for a particular sampler (in example here sampler3).