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
Keep exploring
SQL quizzes
Cosmo
Sign Up Now
or Subscribe for future quizzes