# Aggregate Functions

## regr_slope(Y, X) : slope of the least-squares-fit linear equation determined by the (X, Y) pairs

To illustrate how to use regr_slope(Y,X), I applied it to a real world problem. In Java, if you don't clean up memory properly, the garbage can get stuck and fill up the memory. You dump statistics every hour about memory utilization of different classes and load it into a postgres database for analysis.

All memory leak candidates will have a trend of consuming more memory as more time passes. If you plot this trend, you would imagine a line going up and to the left:

Suppose you have a table containing heap dump histogram data (a mapping of classes to how much memory they consume):

To compute the slope for each class, we group by over the class. The HAVING clause > 0 ensures that we get only candidates with a positive slop (a line going up and to the left). We sort by the slope descending so that we get the classes with the largest rate of memory increase at the top.

Output:

From the output we see that java.util.ArrayList's memory consumption is increasing the fastest at 71.799 bytes per second and is potentially part of the memory leak.

## Simple statistics: min(), max(), avg()

In order to determine some simple statistics of a value in a column of a table, you can use an aggregate function.

If your `individuals`

table is:

Name | Age |
---|---|

Allie | 17 |

Amanda | 14 |

Alana | 20 |

You could write this statement to get the minimum, maximum and average value:

Result:

min | max | avg |
---|---|---|

14 | 20 | 17 |

## string_agg(expression, delimiter)

You can concatenate strings separated by delimiter using the `string_agg()`

function.

If your `individuals`

table is:

Name | Age | Country |
---|---|---|

Allie | 15 | USA |

Amanda | 14 | USA |

Alana | 20 | Russia |

You could write `SELECT ... GROUP BY`

statement to get names from each country:

Note that you need to use a `GROUP BY`

clause because `string_agg()`

is an aggregate function.

**Result:**

names | country |
---|---|

Allie, Amanda | USA |

Alana | Russia |

More PostgreSQL aggregate function described here