Solved

SQL help on a complicate query

Posted on 2013-01-18
2
285 Views
Last Modified: 2013-01-18
I have a table that contains vehicle information for owners.  The table has an ownerid to map vehicles to owners.  What I need is a query that will tell me how many owners have more than 3 vehicles.  I don't need the details, just the count.  So for example:

Vehicle Table
VID       OID
1           100
2           100
3           200
4           300
5           300
6           300
7           300
8           400
9           400
10         400
11         500
12         500
13         500
14         500
15         500
16         500

The SQL would return 2 - OID 300 and OID 500 have more than 3 vehicles.  Can anyone help me with this?  We are using MS SQL Server 2005.
0
Comment
Question by:dyarosh
2 Comments
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 38793760
select OID, count(VID)
from table
group by OID
having count(VID) >= 3


or are you looking for

select count(OID) from (
      select OID
      from table
      group by OID
      having count(VID) >= 3
) a

and you can also try

select count(OID) from (
      select OID, row_number() over (partition by OID order by OID) rn
      from table
) a
where rn >= 3
0
 

Author Closing Comment

by:dyarosh
ID: 38793772
Thank you.  I knew I had to use a Group BY but didn't know where.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SHOWPLAN permission denied in database 'AdventureWorks'. 13 104
Divide by zero error encountered. 2 40
Caste datetime 2 57
Unable to save view in SSMS 21 70
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

810 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