taking single line from duplicate line

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)

taking single line from duplicate line

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-05-26 : 06:39:38

i have lines like this :
col1 col2 col3 col4
0c353897 20090525090954 489614 2
10f3842e 20090525142153 3672431 2
10f3842e 20090525142153 3672431 2
i want to count for example how many times in col4 the number 2 appears.but on duplicate rows (like rows 2 & 3),where a duplicate row is where col1+col2+col3 on each row are the same, i want to count the number 2 only once!

Thnaks for the help
Peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-26 : 06:41:45

select col1,col2,col3, count(distinct col1)as col4
from tablename group by col1,col2,col3

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-26 : 06:42:29

[code]
select count(*)
from
(
select col1, col2, col3, col4,
row_no = row_number() over (partition by col1, col2, col3 order by col4)
from table
) t
where row_no = 1
and col4 = 2
[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-26 : 06:43:33

or maybe you just need to use dense_rank() to rank col4 ?

KH
[spoiler]Time is always against us[/spoiler]

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-05-26 : 06:45:27

thnaks khtan, by the way how do i do this on sql2000 (where you dont have row_number() )?
bklr - i didnt understand why did you do : count(distinct col1)as col4??

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-26 : 06:47:38

quote:
Originally posted by pelegk2

thnaks khtan, by the way how do i do this on sql2000 (where you dont have row_number() )?
bklr - i didnt understand why did you do : count(distinct col1)as col4??

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Well ... you posted in a T-SQL (2005) forum so i assumed you are using SQL 2005 if not .. will be upgrading to 2005 ?

Note : row_number(), dense_rank() is not available on SQL 2000

KH
[spoiler]Time is always against us[/spoiler]

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-05-26 : 06:48:30

what i actually need in the answer is to get how many are from col4 with value 1,2,3,4,5
withought counting duplicate rows?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-05-26 : 06:49:08

quote:
Originally posted by khtan

quote:
Originally posted by pelegk2

thnaks khtan, by the way how do i do this on sql2000 (where you dont have row_number() )?
bklr - i didnt understand why did you do : count(distinct col1)as col4??

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Well ... you posted in a T-SQL (2005) forum so i assumed you are using SQL 2005 if not .. will be upgrading to 2005 ?

Note : row_number(), dense_rank() is not available on SQL 2000

KH
[spoiler]Time is always against us[/spoiler]

yes i know:)
to repost on sql2000 forum?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-26 : 06:49:31

for SQL 2000 version

You just need a count of now many rows col4 = 2 right ?


select count(*)
from
(
    select col1, col2, col3
    from   yourtable
    where  col4 = 2
    group by col1, col2, col3
) t

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-26 : 06:51:33

quote:
to repost on sql2000 forum?

leave it . . . MOD may move it to the appropriate forum.

quote:
what i actually need in the answer is to get how many are from col4 with value 1,2,3,4,5
withought counting duplicate rows?


select col4, count(*)
from
(
    select col1, col2, col3, col4
    from   yourtable
    group by col1, col2, col3, col4
) t
group by col4

KH
[spoiler]Time is always against us[/spoiler]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-26 : 06:52:00

[code]
declare @tab table(col1 varchar(32),col2 varchar(32), col3 int, col4 int)
insert into @tab select
'0c353897', '20090525090954', 489614, 2 union all select
'10f3842e', '20090525142153', 3672431, 2 union all select
'10f3842e', '20090525142153', 3672431, 2

-- differenct values count
select col1,col2,col3, count(distinct col1)as cnt
from @tab group by col1,col2,col3

-- count of records in ur table
select count(col4) from (select col1,col2,col3, count(distinct col1)as col4
from @tab group by col1,col2,col3)s

[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources