SQL Aggregate Functions (Count)

Here we will simply adapt a previously created SQL statement from our BuildingTableAdapter to incorporate a simple COUNT function, totalling the number of each Location for the Building.

This will allow an individual site to display all its Buildings (and only Buildings that belong to it) with a count of all their Locations (and only Locations belonging to those buildings.

This DAL method is used in the 'Forms with List Views' tutorial.

Access our Data Access Layer (Data Set) and select to 'Configure...' the recently created 'FillBySiteID, GetBySiteID (@siteid)' query in the BuildingsTableAdapter.

We don't really need the SiteID in this query as it is being filtered by the same field. This means all returned records should share the same SiteID.

Delete this field from the SQL statement, but keep the preceding comma.

We will now enter in our replacement aggregate function to COUNT all the Locations for a given BuildingID. 

We encase our embedded SELECT statement in brackets and follow it with our calculated field name (AS LocationCount).

We are counting LocationID, but we could equally just enter 'COUNT (*)' and have a similar effect. It is also useful to let this be named 'LocationCount' (Visual Studio will otherwise assign the name of 'Expr1'.

The adapted SQL is shown below with an image of the query wizard pane.

SELECT	BuildingID, BuildingName,
	(SELECT COUNT (LocationID) AS LocationCount
	FROM Locations
	WHERE (BuildingID = Buildings.BuildingID)) AS LocationCount
FROM	Buildings
WHERE	(SiteID = @siteid)

There we have a basic SQL aggregate function for counting records. You may also like to review other aggregates such as SUM and AVG.

Well done. You may like to use this in the Forms with List Views tutorial in the Dynamic Data series.