Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

SQL help on a complicate query

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
dyarosh
Asked:
dyarosh
1 Solution
 
ralmadaCommented:
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
 
dyaroshAuthor Commented:
Thank you.  I knew I had to use a Group BY but didn't know where.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now