Aggregations

Understand aggregating Kafka topic data with Lenses SQL Snapshot concepts engine for Apache Kafka

Count the records

Using the COUNT aggregate function you can count the records in a table. Run the following SQL to see how many records we have on the customers_partitioned:

SELECT 
    COUNT(*) AS total
FROM customers_partitioned

Use SUM to aggregate your amounts

Using the SUM function you can sum records in a table.

SELECT 
    SUM(quantity * price) AS amount
FROM groceries

Group data with GROUP BY

To group data use the GROUP BY clause:

CREATE TABLE customer (
    firstName STRING
    , lastName STRING
    , city STRING
    , country STRING
    , phone STRING) 
FORMAT(string, avro) 
properties(compacted=true);

INSERT INTO customer (
    _key
    ,firstName
    , lastName
    , city
    , country
    , phone)
VALUES
('1','Craig', 'Smith', 'New York', 'USA', '1-01-993 2800'),
('2','William', 'Maugham','Toronto','Canada','[phone]'),
('3','William', 'Anthony','Leeds','UK', '[phone]'),
('4','S.B.','Swaminathan','Bangalore','India','[phone]'),
('5','Thomas','Morgan','Arnsberg','Germany','[phone]'),
('6','Thomas', 'Merton','New York','USA', '[phone]'),
('7','Piers','Gibson','London','UK', '[phone]'),
('8','Nikolai','Dewey','Atlanta','USA','[phone]'),
('9','Marquis', 'de Ellis','Rio De Janeiro','Brazil','[phone]'),
('10','Joseph', 'Milton','Houston','USA','[phone]'),
('11','John','Betjeman Hunter','Sydney','Australia','[phone]'),
('12','Evan', 'Hayek','Vancouver','Canada','[phone]'),
('13','E.','Howard','Adelaide','Australia','[phone]'),
('14','C. J.', 'Wilde','London','UK','[phone]'),
('15','Butler', 'Andre','Florida','USA','[phone]');

Let’s see how many customers there are from each country. Here is the code which computes that:

SELECT 
    COUNT(*) AS count
    , country
FROM customer
GROUP BY country

/* Output
2    Canada
3    UK
2    Australia
1    India
1    Brazil
5    USA
1    Germany
*/