How to: Count Rows in a Table (Visual Database Tools)
You can count rows in a table to determine:
- The total number of rows in a table, for example, a count of all the books in a
titlestable. - The number of rows in a table that meet a specific condition, for example, the number of books by one publisher in a
titlestable. - The number of values in a particular column.
When you count values in a column, nulls are not included in the count. For example, you might count the number of books in a titles table that have values in the advance column. By default, the count includes all values, not just unique values.
The procedures for all three types of counts are similar.
To count all the rows in a table
Be sure the table you want to summarize is already present in the Diagram pane.
Right-click the background of the Diagram pane, then choose Add Group By from the shortcut menu. The Query and View Designer adds a Group By column to the grid in the Criteria pane.
Select * (All Columns) in the rectangle representing the table or table-valued object.
The Query and View Designer automatically fills the term Count into the Group By column in the Criteria pane and assigns a column alias to the column you are summarizing. You can replace this automatically generated alias with a more meaningful one. For more details, see How to: Create Column Aliases (Visual Database Tools).
Run the query.
To count all the rows that meet a condition
Be sure the table you want to summarize is already present in the Diagram pane.
Right-click the background of the Diagram pane, then choose Add Group By from the shortcut menu. The Query and View Designer adds a Group By column to the grid in the Criteria pane.
Select *(All Columns) in the rectangle representing the table or table-structured object.
The Query and View Designer automatically fills the term Count into the Group By column in the Criteria pane and assigns a column alias to the column you are summarizing. To create a more useful column heading in query output, see How to: Create Column Aliases (Visual Database Tools).
Add the data column that you want to search, and then clear the check box in the Output column.
The Query and View Designer automatically fills the term Group By into the Group By column of the grid.
Change Group By in the Group By column to Where.
In the Filter column for the data column to search, enter the search condition.
Run the query.
To count the values in a column
Be sure the table you want to summarize is already present in the Diagram pane.
Right-click the background of the Diagram pane, then choose Add Group By from the shortcut menu. The Query and View Designer adds a Group By column to the grid in the Criteria pane.
Add the column that you want to count to the Criteria pane.
The Query and View Designer automatically fills the term Group By into the Group By column of the grid.
Change Group By in the Group By column to Count.
Note
To count only unique values, choose Count Distinct.
Run the query.
See Also
Other Resources
Sorting and Grouping Query Results (Visual Database Tools)
Summarizing Query Results (Visual Database Tools)