APPROX_ COUNT_ DISTINCT
On this page
Aggregate function.
Syntax
APPROX_COUNT_DISTINCT([ DISTINCT ] [<expression_1>,[ <expression_2> ... ]]
Arguments
-
expression_
: returns the total of distinct values as specified.1 -
expression_
: used to group rows.2 This is an optional expression.
Return Type
Returns an integer data type.
Examples
The following table and values were used for the examples:
CREATE TABLE acd_assets(asset_id int,asset_type varchar(50),asset_desc varchar(50),asset_value decimal(6,2));INSERT into acd_assets values('1049', 'laptop', 'mac_book_pro', '1999.00'),('49', 'cell_phone', 'iphone_12','879.00'),('1100', 'laptop', 'mac_book_pro','1999.00'),('2037', 'laptop', 'mac_book_air_M2','1199.00'),('58', 'cell_phone', 'iphone_12', '879.00'),('130', 'cell_phone', 'iphone_13', '699'),('210', 'laptop', 'mac_book_pro','2500.00'),('111', 'laptop', 'mac_book_pro','2100.00'),('099', 'laptop', 'mac_book_air_M1','999'),('140', 'cell_phone', 'iphone_13_pro','999.00');
SELECT approx_count_distinct(asset_id) AS approx_distinct_asset_idFROM acd_assets;
+-------------------------+
|approx_distinct_asset_id |
+-------------------------+
| 10 |
+-------------------------+
The GROUP BY
clause can be used with the APPROX_
function.GROUP BY
returns the approximate total of each type of asset from the assets table.
SELECT asset_type, APPROX_COUNT_DISTINCT(asset_id) AS approx_distinct_asset_idFROM acd_assetsGROUP BY asset_type;
+------------+--------------------------+
|asset_type | approx_distinct_asset_id |
+------------+--------------------------+
| cell_phone | 4 |
| laptop | 6 |
+------------+--------------------------+
Last modified: February 23, 2023