trdsql aggregation calculation

Posted on:

Aggregation calculation

Of course, you can aggregate calculations as well as COUNT() for aggregation. SQL has a set of aggregation functions that perform calculations on numbers.

Here we explain with an example of a CSV file like the following.

name,price
apple,100
orange,50
melon,500
apple,90
apple,90
orange,40
orange,40

SUM

Calculate the sum. Add all the price columns.

trdsql -ih "SELECT SUM(price) FROM sample.csv"

910

As I wrote before, trdsql treats columns as text types, so you need to CAST them to numeric types before calculating them. However, when using aggregation functions, implicit CAST may be omitted (depending on the database you use).

If you want to explicitly CAST, do the following.

trdsql -ih "SELECT SUM(CAST(price AS int)) FROM sample.csv"

910

AVG

Calculate the average. It can be calculated by sum / number of cases, but it is easier to understand if you use a function. In this example, the meaning of the average may not be so much.

trdsql -ih "SELECT AVG(CAST(price AS int)) FROM sample.csv"

130

MIN,MAX

Output the minimum and maximum values.

trdsql -ih -oh "SELECT MIN(CAST(price AS INT)),MAX(CAST(price AS INT)) FROM sample.csv"

MIN(CAST(price AS INT)),MAX(CAST(price AS INT))
40,500

MIN and MAX can be used with text types, so you need to explicitly CAST them.

(You may want to know the name of MIN and MAX, but SQL is a bit complicated, so I’ll do it later).

As I wrote last time, aggregation functions can be executed at once.

trdsql -ih -oat "SELECT COUNT(name) as count, COUNT(DISTINCT name) as uniq,  MIN(CAST(price AS INT)) AS min,  MAX(CAST(price AS INT)) as max,  SUM(CAST(price AS INT)) as sum,  AVG(CAST(price AS INT)) as avg FROM sample.csv"

+-------+------+-----+-----+-----+-----+
| count | uniq | min | max | sum | avg |
+-------+------+-----+-----+-----+-----+
|     7 |    3 |  40 | 500 | 910 | 130 |
+-------+------+-----+-----+-----+-----+