Select count of rows with same id (SQL)

Question

1.00/5 (1 vote)

See more:

Hello,

My question is i have a table with many columns in it, but for the sake of this question i have two important columns in it,
1- ID_NO
2- CurrentStepName

The CurrentStepName has values like IV and EV and in ID_NO column there are some IDs that are repeating for IV and EV.
For example:
ID_NO----------CurrentStepName
1----------------IV
1----------------EV
2----------------IV
3----------------EV
4----------------IV
4----------------EV

If you see in above example IDs 1 and 4 are repeating for both IV and EV.
Now what i want is to eliminate one ID from IV where it is duplicated with EV.
The result that i want is like as follow.

ID_NO----------CurrentStepName
1----------------EV
2----------------IV
3----------------EV
4----------------EV

In above result set the 1 and 4 ID of IV is removed i want it this way. And then i want to calculate the total count of CurrentStepName.

I hope i have made myself clear.
Looking forward to your help as always.

What I have tried:

Tried many things but still unable to get the result

Posted 20-Feb-17 19:34pm

Faran Saleem

Updated 20-Feb-17 20:39pm

Add a Solution

3 solutions

Solution 1

Google Search is your friend... Here is what a quick search turned up: SQL WHERE Clause[^]. So:

SELECT .... WHERE ID='?'

Posted 20-Feb-17 19:41pm

Graeme_Grant

Comments

Faran Saleem 21-Feb-17 1:44am

There many records i can not use where clause and put all the IDs in there my friend.
I believe a subquery should be used here. I have tried using it but no luck

Graeme_Grant 21-Feb-17 1:47am

Post what you have tried and then we can try and help you from there. We're not here to write your code for you.

Faran Saleem 21-Feb-17 1:50am

You can ask the same politely and i will post the code, do not need to be harsh..

select count(ID_NO) as TotalCount from table1
where currentstepname in ('EV','IV')
group by ID_NO having count(ID_NO)>1

The above gives me the duplicate count of ID_NO but i want to display the CurrentStepName values as well.

Graeme_Grant 21-Feb-17 1:57am

If you read the posting guidelines[^], then my question would not have been required.

Google Search is your friend: SQL Server Group By Query Select first row each group[^]. I highly recommend learning how to use it...

Faran Saleem 21-Feb-17 2:01am

I highly recommend you to learn some manners.. and that is not what i am looking for to, if you READ my post carefully then you can see.. Anyways thanks for your help..

Graeme_Grant 21-Feb-17 2:02am

You want help, dont abuse those trying to help you.

Graeme_Grant 21-Feb-17 2:08am

I'm not going to write the code for you but if you look at the answer in the link that was provided, it shows you how to select the first result from a GROUP which is what your question was asking for.

[Edit] here is the link again just in case you missed it the first time: SQL Server Group By Query Select first row each group[^]

Faran Saleem 21-Feb-17 1:44am

And IDs are generated uniquely so i do not even know which IDs to put in where clause if i go by your suggestion,

Graeme_Grant 21-Feb-17 1:47am

IDs are not unique going by your example. What does your table definition look like?

Solution 3

Can you put this (subquery)

select count(ID_NO) as TotalCount from table1
where currentstepname in ('EV','IV')
group by ID_NO having count(ID_NO)>1

into a temp table ? - not sure of the exact syntax because it depends on which DB you are using, but something like

SQL

create temp table multi_IDNOs as select ID_NO, count(ID_NO) as TotalCount from table1
where currentstepname in ('EV','IV')
group by ID_NO having count(ID_NO)>1

then your query becomes (I think)

SQL

select ID_NO, currentstepname from table
where ID_NO in (select ID_NO from multi_IDNOs) and currentstepname = "EV"
union
select ID_NO, currentstepname from table
where ID_NO Not in (Select ID_No from multi_IDNOs)

The original way I thought about this with the subquery, is likely bad performance-wise, if you can do it without it may be nicer

caveat - my SQL is as rusty as, so, use it for guidance/ideas

Posted 20-Feb-17 20:09pm

Garth J Lancaster

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2017-02-20T20:39:00

To be able to detect 'duplicates', you can use ROW_NUMBER()[^] inline function:

SQL

SELECT *, ROW_NUMBER() OVER(PARTITION BY ID_NO ORDER BY CurrentStepName) AS RowNo
FROM YourTable

Above code returns:

ID_NO	CurrentStepName	RowNo
1		EV				1
1		IV				2
2		IV				1
3		EV				1
4		EV				1
4		IV				2

All you need to do is to write query to delete rows with RowNo=2.

Try!