Skip to main content

SingleStoreDB v7.8

APPROX_COUNT_DISTINCT

Aggregate function. Returns the total of non-NULL distinct values in a data set.

Syntax

APPROX_COUNT_DISTINCT([ DISTINCT ] [<expression_1>,[ <expression_2> ... ]]

Arguments

  • expression_1: returns the total of distinct values as specified.

  • expression_2: used to group rows. 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_id 
FROM acd_assets;

****
+-------------------------+
|approx_distinct_asset_id |
+-------------------------+
| 10                      |
+-------------------------+

The GROUP BY clause can be used with the APPROX_COUNT_DISTINCT function. In this example 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_id
FROM acd_assets
GROUP BY asset_type;

****
+------------+--------------------------+
|asset_type  | approx_distinct_asset_id |
+------------+--------------------------+
| cell_phone | 4                        |
| laptop     | 6                        |
+------------+--------------------------+
In this section: