Check out the
headlines
headlines
Aggregate functions
PQL supports the following aggregate functions, similar to SQL:
These functions are also used by agg_function,
but that is secondary to their use as aggregation functions.
Note: There is a subtle interaction between the aggregate functions and the
GROUP BY clause. Because the aggregate functions
return the aggregate of all column values every time they are called, it is impossible to find the
sum for each individual group of column values without using GROUP BY.
count(*)
count(*) — Returns the total count of items.
Syntax
The count(*) function returns
an integer, and takes one expression, which must be the * star:
integer count(expression input)
Return value
The count(*) function returns an integer.
Description
The count(*) function takes one parameter,
which must be the * star. It returns an integer.
Example 1
Query: How many companies have more than 30 devices?
SELECT count(*) FROM /apps/com/my_company/bizint/company/company_stats WHERE device_count > 30
Result: Depending on your database, this query returns a result like this:
604799
Example 2
This example is based on the following information about a set of disks:
row
*
device: /dev/sda1
available: 15576576000
used: 17007235072
size: 34327568384
file_system: /
row
*
device: /dev/scd0
available: 0
used: 455022592
size: 455022592
file_system: /media/cdrom0
row
*
device: /dev/sdb1
available: 282411008
used: 469778432
size: 752189440
file_system: /tmp/liveusb
You can find out the the maximum, minimum, mean, and sums of this data using variations of the following query:
Query: How many disks per device?
SELECT device, COUNT(*) FROM /network/device/disk GROUP BY device
Result: Depending on your database, this query returns a result like this:
row device: /dev/md1 COUNT(*): 1 row device: /dev/scd0 COUNT(*): 2 row device: /dev/sda1 COUNT(*): 5 row device: /dev/sda2 COUNT(*): 1 row device: /dev/sdb1 COUNT(*): 3
MIN()
min() — Returns the minimum value of all values in a set.
Syntax
The min() function returns an
integer, and takes one expression:
integer min(expression input)
Return value
The min() function returns an integer.
Description
The min() function takes one parameter.
Example 1
Based on the data in Example 2, you can find out the maximum, minimum, mean, and sums of this data using variations of the following query:
Query: Find the minimum available disk space.
SELECT device, MIN(available) FROM /network/device/disk GROUP BY device
Result: Depending on your database, this query returns results like this:
row device: /dev/md1 MIN(available): 7013326848 row device: /dev/scd0 MIN(available): 0 row device: /dev/sda1 MIN(available): 78635008 row device: /dev/sda2 MIN(available): 207457771520 row device: /dev/sdb1 MIN(available): 116709376
MAX()
max() — Returns the maximum value of all values in a set.
Syntax
The max() function returns an
integer, and takes one expression:
integer max(expression input)
Return value
The max() function returns an integer.
Description
The max() function takes one parameter.
Example 1
Based on the data in Example 2, you can find out the maximum, minimum, mean, and sums of this data using variations of the following query:
Query: Find the maximum available disk space.
SELECT device, MAX(available) FROM /network/device/disk GROUP BY device
Result: Depending on your database, this query returns results like this:
row device: /dev/md1 MAX(available): 7013326848 row device: /dev/scd0 MAX(available): 0 row device: /dev/sda1 MAX(available): 68170383360 row device: /dev/sda2 MAX(available): 207457771520 row device: /dev/sdb1 MAX(available): 31954976768
MEAN()
mean() — Returns the mean value of all values in a set.
Syntax
The mean() function returns an
integer, and takes one expression:
integer mean(expression input)
Return value
The mean() function returns an integer.
Description
The mean() function takes one parameter.
Example 1
Based on the data in Example 2, you can find out the maximum, minimum, mean, and sums of this data using variations of the following query:
Query: Find the mean value of available disk space.
SELECT device, MEAN(available) FROM /network/device/disk GROUP BY device
Result: Depending on your database, this query returns results like this:
row device: /dev/md1 MEAN(available): 7.01333e+09 row device: /dev/scd0 MEAN(available): 0 row device: /dev/sda1 MEAN(available): 3.08075e+10 row device: /dev/sda2 MEAN(available): 2.07458e+11 row device: /dev/sdb1 MEAN(available): 1.07847e+10
SUM()
sum() — Returns the total sum value of all values in a set.
Syntax
The sum() function returns an
integer, and takes one expression:
integer sum(expression input)
Return value
The sum() function returns an integer.
Description
The sum() function takes one parameter.
Example 1
Based on the data in Example 2, you can find out the maximum, minimum, mean, and sums of this data using variations of the following query:
Query: Find the total sum value of available disk space.
SELECT device, SUM(available) FROM /network/device/disk GROUP BY device
Result: Depending on your database, this query returns results like this:
row device: /dev/md1 SUM(available): 7013326848 row device: /dev/scd0 SUM(available): 0 row device: /dev/sda1 SUM(available): 154037682176 row device: /dev/sda2 SUM(available): 207457771520 row device: /dev/sdb1 SUM(available): 32354097152
HISTOGRAM()
histogram() — Returns the number of items in a bucket.
Syntax
The histogram() function returns
a string and takes a string parameter:
string histogram(string input)
Return value
The histogram() function returns a string.
Description
The histogram() function returns
the number of items in a bucket. By default, up to 20 buckets are returned.
Example 1: Mailbox size distribution
Query: Find the distribution of mailbox size.
SELECT 'up to ' ||format("a/bucket/to"*1024,
'human_bytes') as range, "a/bucket/count"
FROM (SELECT rows histogram(size) as a
FROM /network/device/wmi/exchange_mailbox
ORDER BY "a/bucket/from" desc)
Results: Depending on the database, this query returns results similar to this:
row range: up to 7.81 MB a/bucket/count: 1 row range: up to 7.03 MB a/bucket/count: 1 row range: up to 399.00 KB a/bucket/count: 5 . . .
Example 2: Items in buckets
Query: Find and count the number of items in the to and from buckets.
SELECT rows histogram(size) as a FROM /network/device/wmi/exchange_mailbox
Results: Depending on the database, this query returns results similar to this:
row a/bucket/from: 6800 a/bucket/to: 7199 a/bucket/count: 1 row a/bucket/from: 7200 a/bucket/to: 7599 a/bucket/count: 0 row a/bucket/from: 7600 a/bucket/to: 7999 a/bucket/count: 1 . . .
