Question

Explain why the following two commands produce different results: SELECT DISTINCT COUNT (V_CODE) FROM PRODUCT; SELECT COUNT (DISTINCT V CODE) FROM PRODUCT;

Answer 1

SELECT DISTINCT COUNT(V_CODE) FROM PRODUCT;

This query will first execute COUNT(V_CODE) which will return a single value. Then the DISTINCT will be applied to that single value. Which means it will display the same value again.

SELECT COUNT (DISTINCT V_CODE) FROM PRODUCT;

This query will COUNT DISTINCT values in the V_CODE column.

For example if SELECT V_CODE FROM PRODUCT; returns a table like this:

V_CODE

Red

Blue

Red

Red

Blue

Blue

Blue

And if you apply SELECT DISTINCT COUNT(V_CODE) FROM PRODUCT; this will return 7 as the output.

Where as if you apply SELECT COUNT (DISTINCT V_CODE) FROM PRODUCT; this will count distinct V_CODES in the table, which means it will return the value 2 as the output.