Archives of the TeradataForum

Message Posted: Wed, 23 Jan 2003 @ 00:30:28 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Dave,

You have M1 P1 ABC in two rows - 1 distinct set of values
You have M2 P2 ABC one time - 1 distinct set of values
You have M2 P2 DEF one time - 1 distinct set of values

That's three distinct sets of values. DISTINCT applies to all columns of the query until V2R5, at which time you will have the opportunity to select multiple distinct counts within a single select statement.

Now, if what you want is the number of distinct Countx values you have by Mode and Plant, you could do something like:

sel tmp1.md, tmp1.pl, count(*)
from (select Mode, Plant, Countx group by 1,2,3) as tmp1 (md, pl, cntx)
group by 1,2
order by 1,2;

The result would be:

M1 P1 1 - the set consists of M1 P1 ABC after the group by in the derived table

M2 P2 2 - the set consists of M2 P2 ABC, and M2 P2 DEF in the derived table

Thus the counts 1 and 2 for the group M1 P1 and M2 P2 respectively.

Sam Mosley
Teradata Certified Master

	<Prev	Next>		<<First	<Prev	Next>	Last>>