Given an Income table which, among others, contains the following two columns and the corresponding data in them:
+--------+------+
| Profit | Loss |
+--------+------+
|   1000 |  100 |
|   2000 | NULL |
|   3000 | 1000 |
|   NULL |  500 |
|   NULL | NULL |
+--------+------+
What value will the following query return:
SELECT COUNT(Profit-Loss)
FROM Income;
Explanation
Arithmetic operators will always return NULL if at least one of its arguments is NULL.
Aggregate functions simply ignore NULL values.
Therefore, 3 values out of 5 will be ignored by COUNT() function.
Theory
  • SQL Aggregate Functions

    SQL aggregate functions return a single value, calculated from values in a column.
    Useful aggregate functions:
    • AVG() - Returns the average value
    • COUNT() - Returns the number of rows
    • FIRST() - Returns the first value
    • LAST() - Returns the last value
    • MAX() - Returns the largest value
    • MIN() - Returns the smallest value
    • SUM() - Returns the sum
    Read more: SQL Functions
  • COUNT() Function

    The COUNT() function returns the number of rows that matches a specified criteria.

    SQL COUNT(column_name) Syntax

    The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:
    SELECT COUNT(column_name) FROM table_name;

    SQL COUNT(*) Syntax

    The COUNT(*) function returns the number of records in a table:
    SELECT COUNT(*) FROM table_name;
    The COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column:

    SQL COUNT(DISTINCT column_name) Syntax

    SELECT COUNT(DISTINCT column_name) FROM table_name;
    Note: COUNT(DISTINCT) works with ORACLE and Microsoft SQL Server, but not with Microsoft Access.
    Read more.

Follow CodeGalaxy

Mobile Beta

Get it on Google Play
Send Feedback