How to design conditions for the SELECT clause

dbForge Query Builder for SQL Server allows you to set up conditions for the queries using the Tabbed Editor. With the Selection tab, you can specify selection criteria for one or multiple columns retrieved from the table(s).

You can access the Tabbed Editor in one of the following ways:

  • On the Query menu, select Show Tabbed Editor.
  • On the Query toolbar, click Insert a column in the query Show Tabbed Editor.

Show Tabbed Editor

Set up conditions for the SELECT clause

On the Selection tab, you can do the following:

  • Select the Unique records checkbox to return only distinct values in the column
  • Remove columns or change their order by using the corresponding controls on the left of the grid
  • Choose a column to retrieve data from
  • Specify an alias for the column
  • Add scalar functions to the column
  • Specify aggregate functions for the column
  • Arrange retrieved data for columns in an ascending or descending order
  • Specify the WHERE criteria for the retrieved data of columns

Choosing columns for the query

Let’s see how to choose a column for the query using the grid in the Tabbed Editor. When you drag the table onto the diagram, all columns are automatically selected and marked as * on the Selection tab of the Tabbed Editor.

If you want to select specific columns for the query, do the following:

1. On the grid, click the row with *.

2. In the Table & Functions dialog that opens, double-click the column you want to add.

3. Optional: To add the function to the column, double-click the corresponding function from the drop-down list.

4. Optional: To add the operator to the expression, double-click the operator from the dialog.

Add a column, function, or operator

5. To create a subquery for the column, click Create Subquery in the row or on the Query toolbar. For more information about working with subqueries, see How to work with subqueries.

6. In the Alias column, specify the alias for a table column.

7. In the Table column, select the table from which you want to retrieve the column.

You can specify the alias for the table on the diagram. To do so, double-click the table header in the shape on the diagram, type the alias, and press Enter.

Add the alias for the table

8. In the Aggregate column, select the aggregate function for the column.

The list of aggregate functions supported in the tool:

Aggregate function Description
avg Calculates the average amount for the selected values in the column.
binary_checksum Returns the checksum as a binary value for a row or particular columns of the table.
checksum_agg Returns the checksum of the values in a table as an integer.
count Counts the total amount of rows in a particular column.
count_big Counts the number of items in the SELECT statement as a big integer data type including NULL and duplicate values.
grouping Calculates the values in the group of the column in the SELECT statement.
grouping_id Calculates the level of grouping.
max Returns the highest value in a particular column.
min Returns the lowest value in a particular column.
stdev Calculates the standard deviation for all values retrieved in the SELECT statement.
stdevp Calculates the standard deviation for all values in the expression based on the entire data population.
sum Returns the sum of all NON-NULL values in a particular column.
var Returns the variance of all variables in the SELECT statement.
varp Returns the variance of all variables in the SELECT statement based on the entire data population.

9. In the Order column, specify the order of values arranged in the column.

10. In the Where column, enter the criteria based on which the data in the column will be filtered.

11. When switching to the Text view, you can preview the SELECT statement.

Preview the query