Duplicate Name Count - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Duplicate Name Count

Author

Topic

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2009-11-03 : 07:02:57

Hi all,

Name

abc
bcd
abc
abc
bcd

In above table abc has repeated 3 times and bcd as 2 so on.
output expected as

Name Count
abc 3
bcd 2
Total 5

I have tried row_number() fn and all that i could not count these repeated counts.at last Total field to be added and corresponding count be added like i shown in the o/p sec..pl help me to sort out this.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-03 : 07:11:23

select name,count(*) from your_table
group by name

Madhivanan

Failing to plan is Planning to fail

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2009-11-03 : 07:16:32

Thanks madivanan,

i was confused.i need to add Total and count at the last..

pl see my post once i again pl.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-03 : 07:18:15

quote:
Originally posted by haroon2k9

Thanks madivanan,

i was confused.i need to add Total and count at the last..

pl see my post once i again pl.

Where do you want to show the result?

Madhivanan

Failing to plan is Planning to fail

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2009-11-03 : 07:19:43

At the end of the Result like below

Name Count
abc 3
bcd 2
Total 5

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-03 : 07:31:07

quote:
Originally posted by haroon2k9

At the end of the Result like below

Name Count
abc 3
bcd 2
Total 5

I meant if you wanted to show the result in any front end application

Madhivanan

Failing to plan is Planning to fail

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2009-11-03 : 07:37:20

Not like that,
select name,count(*) from your_table
group by name

If i execute it,its returns the resultset.now i need to show the Text like Total at the end of name Field and total count as to show at the end of the count field as like this.

Name Count
abc 3
bcd 2
Total 5

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-11-03 : 07:53:24

Try Like this,

select val,count(val) as sum_val from #temp group by val union
select 'total',sum(sum_val) from
(select val,count(val) as sum_val from #temp group by val) t

But Front end does it better!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-03 : 07:59:03

select
case
when (GROUPING([NAME])=1) then 'Total'
else [Name]
end as [Name],
count(*) as [Count]
from your_table
group by [Name]
with cube

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-03 : 08:29:22

quote:
Originally posted by senthil_nagore

Try Like this,

select val,count(val) as sum_val from #temp group by val union
select 'total',sum(sum_val) from
(select val,count(val) as sum_val from #temp group by val) t

But Front end does it better!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Val should be converted to VARCHAR to fit with 'total'

Madhivanan

Failing to plan is Planning to fail

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-11-03 : 08:36:00

quote:
Originally posted by madhivanan

quote:
Originally posted by senthil_nagore

Try Like this,

select val,count(val) as sum_val from #temp group by val union
select 'total',sum(sum_val) from
(select val,count(val) as sum_val from #temp group by val) t

But Front end does it better!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Val should be converted to VARCHAR to fit with 'total'

Madhivanan

Failing to plan is Planning to fail

Ya ofcourse here val is a varchar files which hold 'abc','bcd' ect.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

dhextercorpuz
Starting Member

1 Post

Posted - 2009-12-08 : 15:47:06

SELECT NAME, COUNT(*) AS COUNT FROM YOURTABLE
GROUP BY NAME
UNION
SELECT 'TOTAL' AS NAME, COUNT(*) AS COUNT FROM YOURTABLE

Sharing of knowledge is the most noble things to do...

vikky
Yak Posting Veteran

54 Posts

Posted - 2009-12-09 : 04:16:34

hi,

declare @tab table (name varchar(12))
insert into @tab
select 'abc' union all
select 'bcd' union all
select 'abc' union all
select 'abc' union all
select 'bcd'

select count(name),name
from @tab
group by name
compute sum(count(name) )

Thanks,
vikky.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-12-09 : 06:26:35

Compute will be depricated with the next version of SQL SERVER. It is recommended that you do not use it.
Fred gave an answer a MONTH ago. Any particular reason to dig this up?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources