Have you ever wanted to create reports finding the average donation amount? Have you ever wanted to combine two fields together? If you answered yes to any of these ideas, this article is for you.

Before you get started, here's how to access the Column Manager

In the Bytespree Studio data explorer, the very first column of the table contains a cog icon (pictured below).

Clicking this icon will open the Column Manager.

Use the built-in COUNT(*) aggregate

If your objective is to count the total number of records, toggling the count(*) column (it should be the very first in the list) on will create a count__records column in your query results.

If you're hoping to count distinct combinations of records across a number of other selected column, make sure to subsequently apply a grouping by clicking the "Group result set together" column control (pictured below).

Toggling grouping on will deduplicate the records in your query based on all columns except any aggregate columns.

Create your own aggregate columns

If you're hoping to add something other than the count of records to your result set, you can create your own aggregate columns using the + Add a column link in the footer of the Column Manager.

After clicking + Add a column, give your column a name, and write the SQL expression for the aggregation. Here are some examples assuming a column called amount exists in the results.

AVERAGE(amount) will calculate the average of all values in the amount column.

SUM(amount) will calculate the sum of all values in the amount column.

MIN(amount) will calculate the minimum value in the amount column.

MAX(amount) will calculate the maximum value in the amount column.

NOTE: Most aggregates require the column's data type to be numeric. If you try to apply an aggregate and it doesn't work, try appending ::numeric to the column name, such as AVERAGE(amount::numeric).

Concatenate (combine) columns together

If you have columns package_code and list_code and want to combine them together to create one long string, click + Add a column, give it a name, and type package_code || list_code.

If you have columns first_name and last_name and want to combine them together while adding a space in the middle, click + Add a column, give it a name, and then type first_name || ' ' || last_name. Keep in mind that single quotes must be used. Double quotes will not work for string expressions.

Using advanced CASE statements

CASE statements are great for creating if/else conditions in the data.

Let's say we have column communication_preference which contains values e, m, and p which represent "email", "mail", and "phone" respectively. We could translate these values into their descriptive equivalents using a CASE statement. First, click + Add a column. Next, give your new column a name. Finally, add the following expression: