Is there any max value limit that can be held by COUNT(*) aggregate function. I remember at one of our client sites that COUNT(*) on a table returned negative number and got to know that because the table had so many rows that the rowcount exceeded the max value that can be held by COUNT.
What is the data type used internally for COUNT, if so what is the max limit/value acceptable.
The Teradata documention is your friend here. See the volume "SQL Functions, Operators, Expressions, and Predicates" in the SQL documentation set.
The result type of COUNT(*) depends on the session mode (ANSI or Teradata) and (in ANSI mode) on the MaxDecimal system DBSCONTROL parameter. In Teradata mode the default result type is integer, but a cast expression like CAST(COUNT(*) AS BIGINT) can be used to override the default.