asked on

SQL Server - Count Rows and Where Row Count = 1

I have a table called Asset that has the following attributes:

Assetnum
Description
Location

I want to group this by location and show only locations that have exactly one assetnum. If a location has more than one asset, it should be exclued from the result set. I need the result set to show the location, the assetnum, and the description

Here is an example of the table

------------------------------------------------------------
Assetnum Description Location
------------------------------------------------------------
0101 Asset 0101 A
0102 Asset 0102 B
0103 Asset 0103 C
0104 Asset 0104 B
0105 Asset 0105 B
0106 Asset 0106 A
0107 Asset 0107 D
-------------------------------------------------------

The result that I am looking for is:

----------------------------------------------------------------------------------------
Assetnum Description Location Count of Assets
----------------------------------------------------------------------------------------
0103 Asset 0103 C 1
0107 Asset 0107 D 1
-----------------------------------------------------------------------------------------

Vitor Montalvão

SELECT Assetnum, Description, Location, COUNT(*)
FROM Asset
WHERE <YourSearchCriteria>
GROUP BY Assetnum, Description, Location
HAVING COUNT(*)=1

Open in new window

cbridgman

ASKER

That query returns every row in the Asset table. We have 2500 assets (rows) in our table. Each asset is associated with a location. If you look through the table and group by location, you will find that some locations appear in many rows while other locations appear in only 1 row. In my example, Locations A and B appear in multiple rows. Locations C and D appear in only one row. C & D are the the ones I want returned, not A & B.

Does that make sense?

Mike Eghtebas

Adding to the above solution:

SELECT Assetnum, Description, Location, COUNT(*)
FROM Asset
WHERE <YourSearchCriteria>
GROUP BY Assetnum, Description, Location
HAVING COUNT(*)=1 AND Location IN(D, C)

cbridgman

ASKER

What does the IN(D,C) mean in the Having clause? I get an error when i run it that tells me D and C are invalid column names

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cbridgman

ASKER

Thanks Scott, that did the trick

Mike Eghtebas

SELECT Assetnum, Description, Location, COUNT(*)
FROM Asset
WHERE Location IN(D, C)
GROUP BY Assetnum, Description, Location
HAVING COUNT(*)=1

In(D, C) is to ignore other locations.