Aggregate functions
An aggregate function in SQL performs a calculation on a set of rows and returns a single summary value.
Database Explorer lets you perform operations on individual or multiple aggregate functions using the shortcut menu. Right-click a function in Database Explorer to access options for tasks such as creating, managing, or debugging functions. You can also generate scripts for them.
Create an aggregate function
1. In Database Explorer, right-click an aggregate function or the Aggregate Functions node and select New Aggregate Function.
2. In a SQL document, fill in the CREATE AGGREGATE statement template.
3. At the bottom of the SQL document, click Apply Changes.
Tip
To create an aggregate function, right-click the Functions > Aggregate Functions node and select New Aggregate Function.
Edit an aggregate function
1. In Database Explorer, right-click the aggregate function to edit and select Open Editor.
2. In a SQL document, edit the function.
3. At the bottom of the SQL document, click Apply Changes.
Alternatively, in the top menu, select File > Save or press Ctrl+S.
Delete an aggregate function
1. In Database Explorer, right-click the aggregate function to delete and select Delete, or press Delete.
2. Click Yes to confirm the deletion.
Note
You cannot undo the drop function operation, as this action permanently deletes all its data and structure. It is recommended that you back up the database before deleting it.
Duplicate an aggregate function
1. In Database Explorer, right-click the required aggregate function and select Duplicate Object.
2. In the Duplicate Object dialog, configure the object settings:
- In Destination connection, select the target server.
- In Destination database, select the target database.
- In Destination schema, select the target schema.
- In New object name, specify the object name.
- Select Drop destination object to drop the destination object if it already exists.s
- Optional: Select Script Changes, then select To New SQL Window to open the generated script in a new SQL document, or To Clipboard to copy it to the clipboard.
3. Click OK.
Generate a script for an aggregate function
1. In Database Explorer, right-click the required aggregate function and select Generate Script As, then select the statement type.
2. Select the output option:
- To New SQL Window – Opens the script in a new SQL document.
- To File – Generates the script to a .sql file.
- To Clipboard – Copies the script to the clipboard.
For more information, see Generate the DDL and DML statements for database objects.
Add an aggregate function to the database diagram
To add an aggregate function to the database diagram: In Database Explorer, right-click the required function and select Send To > Database Diagram.
View function properties
To view function properties, in Database Explorer, right-click the function or Aggregate Functions node and select Show Details.
Object Viewer opens, displaying detailed information about the function.
Refresh an aggregate function
To update the function to reflect the latest changes, in Database Explorer, right-click the function or Aggregate Functions node and select Refresh, or press F5.
Open the Properties window
To open the Properties window, in Database Explorer, right-click the aggregate function and select Properties, or press F4.
Filter aggregate functions
1. In Database Explorer, right-click the Aggregate Functions node and select Filter.
2. In the Filter Settings dialog, configure the filter parameters.
3. Click OK to apply the filter.
Note
When you open the Aggregate Functions node, the number of objects it contains appears in parentheses next to the node name. This number changes based on your filter settings.
Shortcut menu options for a specific aggregate function
The following table describes the shortcut menu options, which appear when you right-click a specific aggregate function in Database Explorer.
| Name | Description |
|---|---|
| New Aggregate Function | Opens a CREATE AGGREGATE template in a new SQL document to create an aggregate function. |
| Open Editor | Opens the Aggregate Function designer to create the function or change its code. |
| Source Control > Source Control Manager | Opens Source Control Manager, where you can track and manage changes to database schema and static data. When Source Control Manager is already open, this option refreshes it. |
| Source Control > Commit | Opens Source Control Manager with all local changes (if any) selected and ready to commit. When Source Control Manager is already open, this option refreshes it and selects all local database changes. |
| Source Control > Get Latest | Opens Source Control Manager with all remote changes (if any) selected, ready to pull using Get Latest. When Source Control Manager is already open, this option refreshes it and selects all remote database changes. |
| Duplicate Object | Opens the Duplicate Object dialog to duplicate the function. |
| Generate Script As | Generates a script of the selected object using the CREATE, DROP, DROP and CREATE, and SELECT statements. The script can be saved to a new SQL document or to a file, or copied to the clipboard. |
| Send to > Database Diagram | Adds the selected function to a database diagram. |
| Show Details | Opens Object Viewer to view object details. |
| Delete | Drops the selected function from the database. Shortcut: Delete |
| Refresh | Updates the object to reflect the latest changes. Shortcut: F5 |
| Properties | Opens the Properties pane to view the object information. Shortcut: F4 |
Shortcut menu options for the Aggregate Functions node
The following table describes the shortcut menu options, which appear when you right-click the Aggregate Functions node in Database Explorer.
| Name | Description |
|---|---|
| New Aggregate Function | Opens a new SQL document containing the CREATE AGGREGATE statement. |
| Source Control > Source Control Manager | Opens Source Control Manager, where you can track and manage changes to database schema and static data. When Source Control Manager is already open, this option refreshes it. |
| Source Control > Commit | Opens Source Control Manager with all local changes (if any) selected and ready to commit. When Source Control Manager is already open, this option refreshes it and selects all local database changes. |
| Source Control > Get Latest | Opens Source Control Manager with all remote changes (if any) selected, ready to pull using Get Latest. When Source Control Manager is already open, this option refreshes it and selects all remote database changes. |
| Filter | Opens the Filter Settings dialog to control which aggregate functions should appear in Database Explorer. |
| Show Details | Opens Object Viewer to view object details. |
| Refresh | Updates the object to reflect the latest changes. Shortcut: F5 |
Note
The Source Control shortcut menu is available only after you link the corresponding database to a source-control system.
Bulk operations
In Database Explorer, hold down Ctrl and select multiple aggregate functions. Then right-click the selection and select the required option.
The following table describes the shortcut menu options available for multiple aggregate functions.
| Name | Description |
|---|---|
| Open Editor | Opens the Aggregate Function designers for each selected function. |
| Source Control > Source Control Manager | Opens Source Control Manager, where you can track and manage changes to database schema and static data. When Source Control Manager is already open, this option refreshes it. |
| Source Control > Commit | Opens Source Control Manager with all local changes (if any) selected and ready to commit. When Source Control Manager is already open, this option refreshes it and selects all local database changes. |
| Source Control > Get Latest | Opens Source Control Manager with all remote changes (if any) selected, ready to pull using Get Latest. When Source Control Manager is already open, this option refreshes it and selects all remote database changes. |
| Source Control > Link/Unlink Static Data | Opes the Source Control Link Static Data dialog that displays the tables whose data you want to link. |
| Generate Script As | Generates scripts of the selected objects using the CREATE, DROP, DROP and CREATE, and SELECT statements. The scripts can be generated to a new SQL document or to a file, or copied to the clipboard. |
| Send to > Database Diagram | Adds the selected functions to a database diagram. |
| Delete | Drops the selected functions from the database. Shortcut: Delete |
