Data type for SQL aggregate functions (COUNT, SUM, AVG)

New to SQL. Say I run this query:

SELECT COUNT(SupplierID) AS "TotalSuppliers"
FROM Products;

This will return a single row with a field called “TotalSuppliers”. In my database, the result was “778”.

When tested in C# using typeof, the type for this field was short (the ODBC SMALLINT or OleDB SmallInt data type). This matched the data type of the SupplierID column.

Do I have control over the type used for the alias/temporary column? For example, is it possible to run COUNT or SUM on a column that is ODBC SMALLINT and use the INTEGER or DOUBLE data type in the output?

Answer

You can convert in the query:

select cast(count(*) as double) as TotalSuppliers

However, this is the type in the database. ODBC is still responsible for the conversion to your application type.

Note: In most databases, count(*) should be returning an int — usually 4 bytes. I wonder if on your system smallint is really 4 bytes.