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.
+ 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
Concatenate (combine) columns together
If you have columns
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
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.
CASE statements are great for creating if/else conditions in the data.
Let's say we have column
communication_preference which contains values
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:
WHEN communication_preference = 'e' THEN 'email'
WHEN communication_preference = 'm' THEN 'mail'
WHEN communication_preference = 'p' THEN 'phone'