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
-----------------------------------------------------------------------------------------
cbridgmanAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
SELECT
    MAX(Assetnum,) AS Assetnum, MAX(Description) AS Description,
    Location, COUNT(*) AS [Count of Assets]
FROM Asset
GROUP BY Location
HAVING COUNT(*) = 1
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
SELECT Assetnum, Description, Location, COUNT(*)
FROM Asset
WHERE <YourSearchCriteria>
GROUP BY Assetnum, Description, Location
HAVING COUNT(*)=1

Open in new window

0
 
cbridgmanAuthor Commented:
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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Mike EghtebasDatabase and Application DeveloperCommented:
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)
0
 
cbridgmanAuthor Commented:
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
0
 
cbridgmanAuthor Commented:
Thanks Scott, that did the trick
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.