CODEWITHSUNDEEP
sql
Colin Douglas
Colin Douglas

Reputation: 583

Simple SQL statement

I've been over-thinking this too much. Let's say I have a table TEST(refnum VARCHAR(5))

    |refnum|
    --------
    | 12345|
    | 56873|
    | 63423|
    | 12345|
    | 56873|
    | 12345|

I want my "view" to look something along the lines of this

    |refnum| count|
    ---------------
    | 12345|   3  |
    | 56873|   2  |

So the requirements are that the count for each refnum has to be > 1. I'm having a little trouble wrapping my head around this one. Thank you in advance for the help.

Upvotes: 0

Views: 91

Answers (3)

JBrooks
JBrooks

Reputation: 10013

This is the SQL Server version:

CREATE VIEW vRefnumCounts AS
SELECT refnum, 
count(1) as [count] 
FROM test 
GROUP BY refnum 
HAVING count(1) > 1

SELECT *
FROM vRefnumCounts 
ORDER BY refnum

You said "view", but now I'm thinking you meant in the result set sense...

Upvotes: 0

Christina
Christina

Reputation: 1

select refnum, count(*)
from table
group by refnum

Upvotes: 0

Thilo
Thilo

Reputation: 262474

Unless I am missing something, this looks like a simple

select refnum, count(*) from test group by refnum having count(*) > 1

Upvotes: 11

Related Questions