Home Page for the TeradataForum

Archives of the TeradataForum

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

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

Subj:   Re: Distinct count problem
From:   Sam Mosley


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>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020