Aggregations

YugabyteDB supports a number of standard aggregation functions. Let us go through some of these using an example. Consider a products table as shown below.

ProductIDProductNameSupplierIDCategoryIDUnitPriceQuantity
1Chais1110 boxes x 20 bags1825
2Chang1124 - 12 oz bottles1912
3Aniseed Syrup1212 - 550 ml bottles1010
4Chef Anton’s Cajun Seasoning2248 - 6 oz jars229
5Chef Anton’s Gumbo Mix2236 boxes21.3540

Let us create this table with ProductID as the primary hash key.

  1. cqlsh> CREATE KEYSPACE store;
  1. cqlsh> CREATE TABLE store.products (ProductID BIGINT PRIMARY KEY, ProductName VARCHAR, SupplierID INT, CategoryID INT, Unit TEXT, Price FLOAT, Quantity INT);

Now let us populate the sample data.

  1. INSERT INTO store.products (ProductID, ProductName, SupplierID, CategoryID, Unit, Price, Quantity) VALUES (1, 'Chais', 1, 1, '10 boxes x 20 bags', 18, 25);
  2. INSERT INTO store.products (ProductID, ProductName, SupplierID, CategoryID, Unit, Price, Quantity) VALUES (2, 'Chang', 1, 1, '24 - 12 oz bottles', 19, 12);
  3. INSERT INTO store.products (ProductID, ProductName, SupplierID, CategoryID, Unit, Price, Quantity) VALUES (3, 'Aniseed Syrup', 1, 2, '12 - 550 ml bottles', 10, 10);
  4. INSERT INTO store.products (ProductID, ProductName, SupplierID, CategoryID, Unit, Price, Quantity) VALUES (4, 'Chef Anton''s Cajun Seasoning', 2, 2, '48 - 6 oz jars', 22, 9);
  5. INSERT INTO store.products (ProductID, ProductName, SupplierID, CategoryID, Unit, Price, Quantity) VALUES (5, 'Chef Anton''s Gumbo Mix', 2, 2, '36 boxes', 21.35, 40);

Counts

  • Finding the number of item types in the store can be done as follows.
  1. cqlsh> SELECT COUNT(ProductID) FROM store.products;

  1. count(productid)

  1. 5

(1 rows)

  • We can give an alias name to the count column as follows.
  1. cqlsh> SELECT COUNT(ProductID) as num_products FROM store.products;

  1. num_products

  1. 5

(1 rows)

  • Finding the number of item types for supplier 1 can be done as follows.

You can do this as shown below.

  1. cqlsh> SELECT COUNT(ProductID) as supplier1_num_products FROM store.products WHERE SupplierID=1;

  1. supplier1_num_products

  1. 3

(1 rows)

Numeric aggregation functions

The standard aggregate functions of min, max, sum, avg and count are built-in functions.

  • To find the total number of items in the store, run the following query.
  1. cqlsh> SELECT SUM(Quantity) FROM store.products;

  1. sum(quantity)

  1. 96

(1 rows)

  • To find the price of the cheapest and the most expensive item, run the following.
  1. cqlsh> SELECT MIN(Price), MAX(Price) FROM store.products;
  1. min(price) | max(price)
  2. ------------+------------
  3. 10 | 22
  4. (1 rows)
  • To find the average price of all the items in the store, run the following.
  1. cqlsh> SELECT AVG(price) FROM store.products;

  1. system.avg(price)

  1. 18.07

(1 rows)