How to count distinct values when using GROUP BY

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