Solved

SQL Server - Count Rows and Where Row Count = 1

Posted on 2015-02-10
7
151 Views
Last Modified: 2015-02-10
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
-----------------------------------------------------------------------------------------
0
Comment
Question by:cbridgman
7 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40601098
SELECT Assetnum, Description, Location, COUNT(*)
FROM Asset
WHERE <YourSearchCriteria>
GROUP BY Assetnum, Description, Location
HAVING COUNT(*)=1

Open in new window

0
 

Author Comment

by:cbridgman
ID: 40601125
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
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40601148
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:cbridgman
ID: 40601161
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
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 40601164
SELECT
    MAX(Assetnum,) AS Assetnum, MAX(Description) AS Description,
    Location, COUNT(*) AS [Count of Assets]
FROM Asset
GROUP BY Location
HAVING COUNT(*) = 1
0
 

Author Closing Comment

by:cbridgman
ID: 40601171
Thanks Scott, that did the trick
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40601176
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

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now