1

I have 36 columns in a table but one of the columns have data multiple times like below

ID       Name       Ref
abcd    john doe    123
1234    martina     100
123x    brittany    123
ab12    joe         101

and i want results like 

ID      Name        Ref cnt
abcd    john doe    123 2
1234    martina     100 1
123x    brittany    123 2
ab12    joe         101 1

as 123 has appeared twice i want it to show 2 in cnt column and so on

Stanislovas Kalašnikovas
  • 9,203
  • 4
  • 32
  • 59
Sandeep
  • 13
  • 4

3 Answers3

0

You should provide SQL brand to know capabilities:

1) If your DB supports window functions:

Select
   *,
   count(*) over ( partition by ref ) as cnt 
from your_table

2) If not:

Select
   T.*, G.cnt
from
  ( select * from your_table ) T inner join
  ( select count(*) as cnt from your_table group by ref ) G
  on T.ref = G.ref
dani herrera
  • 48,760
  • 8
  • 117
  • 177
0
select ID, Name, Ref, (select count(ID) from [table] where Ref = A.Ref)
from [table] A

Edit: As mentioned in comments below, this approach may not be the most efficient in all cases, but should be sufficient on reasonably small tables.

In my testing:

  • a table of 5,460 records and 976 distinct 'Ref' values returned in less than 1 second.
  • a table of 600,831 records and 8,335 distinct 'Ref' values returned in 6 seconds.
  • a table of 845,218 records and 15,147 distinct 'Ref' values returned in 13 seconds.
user2585435
  • 131
  • 1
  • 8
  • Take a look to this article about how to [optimizing correlated subqueries](https://en.wikipedia.org/wiki/Correlated_subquery#Optimizing_correlated_subqueries) – dani herrera Oct 22 '15 at 05:58
  • @danihp Interesting. Will keep it in mind if I find performance an issue, but not sure it warranted a downvote? Original Post never hinted at the size of the table. FWIW I got a complete resultset in less than 1 second on a table of 5,460 records and 976 distinct 'Ref'-equivalent values, and 6 seconds for a table of 600,831 records with 8,335 distinct 'Ref'-equivalent values. – user2585435 Oct 22 '15 at 06:16
  • Interesting. Technically I can't remove downvote unless you edit your answer. Perhaps you can include this numbers vs numbers for non correlated query in your answer. – dani herrera Oct 22 '15 at 06:19
  • Also may be a good idea to post execution plan: http://stackoverflow.com/a/141310 – dani herrera Oct 22 '15 at 06:26
  • Thank you very much. Actually while i was doing countif with excel it was taking 3-4 hrs that is why i brought this problem here – Sandeep Oct 22 '15 at 06:57
0

You can use COUNT with OVERin following:

QUERY

select ID, 
       Name, 
       ref,
       count(ref) over (partition by ref) cnt
from #t t

SAMPLE DATA

create table #t
(
ID NVARCHAR(400),
Name NVARCHAR(400),
Ref INT
)

insert into #t values    
('abcd','john doe',    123),
('1234','martina',     100),
('123x','brittany',    123),
('ab12','joe',         101)
Stanislovas Kalašnikovas
  • 9,203
  • 4
  • 32
  • 59