Teradata tutorial for Count(*) Vs Count(1) and other count function.

Teradata: Count(*) Vs Count(1)

• Over the period of time Teradata has worked and made sure that there is no difference between Count(*) and Count(1) / Count(n).
• But this was not true few Teradata release back when Count(*) was showing total number of rows present in the table.
• Count(1) was showing total number of non NULL values in the first column of the table.
• Similarly count(n) was showing non NULL values in nth column of the table.

• Count(*)
• Count(1)
• Count(n)
• Count(column_name)
• Count(distinct column_name)

Create Sample Table: Creating a sample volatile table ledger with 3 columns to validate the statements.


CREATE  VOLATILE TABLE ledger
( 
  year_nr          INTEGER,
  Quarter          VARCHAR(10),
  Sales            DECIMAL(18,0)
)
ON COMMIT PRESERVE ROWS;


Data Population: Inserting few records in the table to validate the statements.


INSERT INTO ledger VALUES(null,'Q1',90);
INSERT INTO ledger VALUES(2015,null,70);
INSERT INTO ledger VALUES(2015,null,130);
INSERT INTO ledger VALUES(2015,'Q4',30);
INSERT INTO ledger VALUES(2016,'Q1',null);
INSERT INTO ledger VALUES(2016,'Q2',null);
INSERT INTO ledger VALUES(2016,'Q3',null);
INSERT INTO ledger VALUES(2016,'Q4',null);


Data View: Checking data in the 'ledger' table

SELECT * FROM ledger;

Output:
year  Quarter   Sales
2015  Q1        90
2015  null      70
2015  null      130
2015  Q4        30
2016  Q1        null
2016  Q2        null
2016  Q3        null
2016  Q4        null

• COUNT(*) : This will return total number of rows present in the table.

  
  SELECT COUNT(*) FROM ledger;
  
     COUNT(*)
  -----------
            8
  

  
  
• COUNT(1) : This will return total number of rows present in the table.

  
  SELECT COUNT(1) FROM ledger;
  
     COUNT(1)
  -----------
            8
  

  
  
• COUNT(2) : This will return total number of rows present in the table.

  
  SELECT COUNT(2) FROM ledger;
  
     COUNT(2)
  -----------
            8
  

  
  
• COUNT(4) : This will return total number of rows present in the table.

  
  SELECT COUNT(4) FROM ledger;
  
     COUNT(4)
  -----------
            8
  
  Note: Even though there are 3 column,count(4) is working because number in count does not represent column order anymore in Teradate.
  

  
  
• COUNT(year_nr) : This will return total number of rows with non NULL values in 'year_nr' column present in the table.

  
  SELECT COUNT(year_nr) FROM ledger;
  
  COUNT(year_nr)
  --------------
               7
  
  Note: Since 1 row has null for year_nr column in ledger table, output is 7 even total rows in the table is 8.
  

  
  
• COUNT(quarter) : This will return total number of rows with non NULL values in 'Quarter' column present in the table.

  
  SELECT COUNT(Quarter) FROM ledger;
  
  COUNT(Quarter)
  --------------
               6
  
  Note: Since 2 row has null for Quarter column in ledger table, output is 6 even total rows in the table is 8.
  

  
  
• COUNT(DISTINCT year_nr) : This will return total number of distinct value rows with non NULL values in 'year_nr' column present in the table.

  
  SELECT COUNT(DISTINCT year_nr) FROM ledger;
  
  COUNT(DISTINCT year_nr)
  -----------------------
                       2
  
  Note: Since only 2 distinct non null values are present in year_nr column in ledger table, output is 2 even total rows in the table is 8.