I want to count distinct values based on a "group by" statement in Teradata and save the values to the column.
Here’s an example of my data after the group by clause:
Team_ID | Individual_ID | Num_Inidividual_Books |
--------------------------------------------------
1 | 02 | 4 |
--------------------------------------------------
--------------------------------------------------
1 | 03 | 1 |
--------------------------------------------------
--------------------------------------------------
1 | 01 | 4 |
--------------------------------------------------
--------------------------------------------------
2 | 05 | 11 |
--------------------------------------------------
--------------------------------------------------
2 | 09 | 11 |
--------------------------------------------------
--------------------------------------------------
2 | 08 | 11 |
--------------------------------------------------
I want to add a "distinct values for grouping" column, so it would look like this:
Team_ID | Individual_ID | Num_Inidividual_Books | Num_Distinct_Book_Amount_Per_Grouping |
------------------------------------------------------------------------------------------
1 | 02 | 4 | 2
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
1 | 03 | 1 | 2
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
1 | 01 | 4 | 2
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
2 | 05 | 11 | 1
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
2 | 09 | 11 | 1
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
2 | 08 | 11 | 1
------------------------------------------------------------------------------------------
I’ve tried using COUNT() and COUNT(DISTINCT) with the GROUP BY clause in my query but I get an error: Cannot nest non-aggregate functions.
I think I am going about it the wrong way. Any suggestions?
Let me know if I need to provide more information.
Thanks!
You need to sign in to view this answers
Leave feedback about this