		Site Navigation
	Home
	Products
	Tags
	Levels
	Dates
	Authors

		5 MOST RECENT
	Click to view Braintrove article: Conditionally Display Different Values In a Single Expression Box Written by Gregory Scot Collins Saturday, 20 March 2010, 8:24 PM Products: InfoPath and Office Using a special little-known XPath expression you can display multiple values in a single expression box rather than in multiple expression boxes with conditional formatting. Conditionally Display Different Values In a Single Expression Box
	Click to view Braintrove article: Create a Button With a Blank Label Written by Gregory Scot Collins Saturday, 20 March 2010, 7:04 PM Products: InfoPath and Office InfoPath 2007 no longer allows you to have a blank label for buttons?useful for creating picture buttons. Discover this easy way to work around this issue. Create a Button With a Blank Label
	Click to view Braintrove article: Create Shared Rules Written by Gregory Scot Collins Monday, 15 March 2010, 12:41 PM Products: InfoPath and Office Avoid the maintenance nightmare of having multiple controls with the same set of rules with this simple trick that allows you to share rules between controls. Create Shared Rules
	Click to view Braintrove article: Perform a Case-Insensitive String Comparison without Using Translate() Written by Gregory Scot Collins Thursday, 20 August 2009, 7:21 PM Products: InfoPath and Office Hidden in undocumented optional parameters of an extended function, there is a way to perform a case-insensitive string comparison without the need for the translate() function. Perform a Case-Insensitive String Comparison without Using Translate()
	Click to view Braintrove article: Force a Field Value to a Boolean Using Rules Written by Gregory Scot Collins Wednesday, 29 July 2009, 9:42 AM Products: InfoPath and Office Custom data validation is not available for fields bound to a secondary data source making it a challenge to validate to a specific data type. Using rules you can force a field value to a Boolean. This is especially useful when standardizing unconventional Boolean values copyied from a secondary data source. Force a Field Value to a Boolean Using Rules

		5 MOST POPULAR
	Click to view Braintrove article: Pass Query String Parameters to an ASP.NET Xml Control Written by Gregory Scot Collins Friday, 19 May 2006, 5:19 PM Product: ASP.NET Use the Page_Load method of your ASPX page to attach a list of parameters to your asp:Xml control that will be passed through to your XSL transform. Pass Query String Parameters to an ASP.NET Xml Control
	Click to view Braintrove article: Access a Method in a Master Page with Code-Behind Written by Gregory Scot Collins Friday, 16 June 2006, 7:09 AM Product: ASP.NET Write code for pages with code-behind to access a method in an immediate or parent master page from a content page. Access a Method in a Master Page with Code-Behind
	Click to view Braintrove article: Prevent Namespace Prefixes from Being Copied to the Output Written by Gregory Scot Collins Monday, 1 May 2006, 7:18 AM Products: HTML, JavaScript, XML, XPath and XSLT The extra namespace prefixes required to transform XML are not always desirable on the output. This article describes a technique for excluding specific namespace prefixes from the output. Prevent Namespace Prefixes from Being Copied to the Output
	Click to view Braintrove article: Create Groups in a Drop-Down List Box Written by Gregory Scot Collins Saturday, 17 June 2006, 5:57 AM Products: CSS, FireFox, HTML and Internet Explorer Create grouped option-sets in your drop-down list boxes, each with its own non-selectable header. Create Groups in a Drop-Down List Box
	Click to view Braintrove article: Change the Default Action of the Power Button on the Windows Vista Start Menu Written by Gregory Scot Collins Friday, 8 December 2006, 5:30 AM Product: Windows In Windows Vista, you can change the default action for the power button on the Start menu, as well as for other power events, in the advanced power settings. Change the Default Action of the Power Button on the Windows Vista Start Menu

		5 MOST FORGOTTEN
	Click to view Braintrove article: Hide Deleted Items in the Source Control Explorer Written by Gregory Scot Collins Friday, 21 December 2007, 12:04 PM Products: Team Foundation Server and Visual Studio When deleted items hinder your view of valid source control items, change the visibility option to hide them. This option can also be reversed to show deleted items when needed. Hide Deleted Items in the Source Control Explorer
	Click to view Braintrove article: Recover a Dark Picture with a Heavy Color Cast Written by Gregory Scot Collins Wednesday, 30 April 2008, 10:41 PM Product: Photoshop Even a picture that seems hopelessly lost has a chance to be recovered. Here we take a picture that is too dark and has a heavy yellow cast and revive it to an acceptable state. Recover a Dark Picture with a Heavy Color Cast
	Click to view Braintrove article: Prevent Windows Vista from Requesting Your Permission to Continue Written by Gregory Scot Collins Monday, 11 December 2006, 4:15 AM Products: Group Policy Object Editor and Windows Windows Vista touts a new security feature that is frequently complained about. Find out how you can disable the dialog box that says Windows needs your permission to continue. Prevent Windows Vista from Requesting Your Permission to Continue
	Click to view Braintrove article: Create a Variable with a Unique Sorted Node-Set Written by Gregory Scot Collins Tuesday, 2 May 2006, 10:27 AM Products: XML, XPath and XSLT Easily create and use a variable with a unique sorted node-set. Create a Variable with a Unique Sorted Node-Set
	Click to view Braintrove article: Prevent Users from Seeing ASP.NET Error Pages Written by Gregory Scot Collins Friday, 21 April 2006, 9:46 AM Product: ASP.NET Create a positive user experience by ensuring that those using your ASP.NET applications are redirecting away from errors to a more friendly page. Prevent Users from Seeing ASP.NET Error Pages

		Microsoft MVP

This article has been viewed 276 times.

Select Table Row Counts as Columns

Written by Gregory Scot Collins

Monday, 31 December 2007, 8:49 AM

This article has been tested to work with the following products and versions. No guarantee of compatibility, with or without modification, is offered for products or versions other than those listed.

SQL Server 2005 (Microsoft)

IN THIS ARTICLE:

If there is a need to identify the number of rows in one or more tables of your SQL database, you can easily get these counts and return them as columns of a SELECT clause. In this article, we will show the basic syntax required for achieving this, and then show an actual example using the AdventureWorks sample database.

Syntax for selecting table row counts as columns

The method of selecting table row counts as columns is simple and straighforward. Listing 1 shows the syntax as performing selects within your main SELECT clause. These inner-SELECT clauses must be contained within parentheses. The COUNT(*) function returns the number of rows in the specified table. The highlighted portions of Listing 1 identify places where you need to substitute in your actual table names and your desired resulting column names.

SELECT
    (SELECT COUNT(*) FROM Table1) AS Column1,
    (SELECT COUNT(*) FROM Table2) AS Column2

Listing 1. Selecting row counts as columns.

Selecting row counts using the AdventureWorks sample database

Now we will apply the syntax to an actual SELECT clause using the AdventureWorks sample database that ships with Microsoft SQL Server 2005. If you do not have this sample database installed, you can install it following the instructions contained within the SQL Server online help. Listing 2 gets the counts of four different tables and names the columns appropriately.

USE AdventureWorks
 
SELECT
    (SELECT COUNT(*) FROM HumanResources.Employee) AS Employees,
    (SELECT COUNT(*) FROM Purchasing.Vendor) AS Vendors,
    (SELECT COUNT(*) FROM Sales.SalesPerson) AS SalesPeople,
    (SELECT COUNT(*) FROM Sales.Store) AS Stores

Listing 2. Selecting row counts as columns from the AdventureWorks sample database.

The result of the SELECT clause in Listing 2 is shown in Screenshot 1. Any number of table row counts can be returned in this manner.

Screenshot 1. Results of the AdventureWorks row count selection.

Copyright ©2007 Braintrove. All rights reserved. This material may not be copied, published, broadcast, rewritten or redistributed. You may, however, use the techniques, along with any associated code and files in your development. This material is provided "AS IS" without warranty of any kind. You accept full responsibility and liability for any and all results associated with use of this material.

		Bookmark this Article
	Stumble It!
	Digg It!
	del.icio.us

		List of Figures
	Listing 1 - Selecting row counts as columns.
	Listing 2 - Selecting row counts as columns from the AdventureWorks sample database.
	Screenshot 1 - Results of the AdventureWorks row count selection.

		Article Tags
	AdventureWorks Count Select SQL SQL Server Use

		Great Deals
	TigerDirect Exclusive Deals, Limited Time Offers, Act Now And Save!
	Find all current special offers on Adobe products.

Copyright © 2006-2010 Braintrove. All rights reserved. Braintrove, braintrove.com, and the Braintrove logo are trademarks of Gregory Scot Collins in the United States and/or other countries. The names of actual companies and products mentioned herein may be the trademarks of their respective owners. Any rights not expressly granted herein are reserved.