query - 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)

query

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-02-11 : 13:25:33

i have this table:
i count for each name how many rows he have, and if he have one row with color red (like A) so he get red


name  row   color
A      1    red
A      2    green
A      3    green
B      1     green
B      2     green
C      1      red

i want to get

name count  color
A      3    red 
B      2    green
C      1     red

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-11 : 13:51:59

This maybe..?

declare @t table (
[name] varchar(1),  row int,   color varchar(10))
insert @t
select 'A' ,     1,    'green'
union all select 'A' ,     2 ,   'green'
union all select 'A' ,     3,     'red'
union all select 'B' ,    1,     'green'
union all select 'B' ,     2,     'green'
union all select 'C',     1,      'red'

select a.[name],a.[cnt],coalesce(b.color,a.color) 
from 
(
select [name],count(*) as cnt,min(color) as color from @t 
group by [name]
) a
left join @t b on a.[name] = b.[name] and b.[color] = 'red'

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-02-11 : 14:03:37

Or maybe like this:

SELECT *
  FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY [name] ORDER BY row DESC) AS Rank
          FROM @t) D
WHERE D.Rank = 1

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-02-11 : 14:24:07

it is not what i need.

under column count, it SELECT count(name).
(and i use sql 2000)

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-11 : 14:46:48

quote:
Originally posted by inbs

it is not what i need.

under column count, it SELECT count(name).
(and i use sql 2000)

which solution are you referring to? ROW_NUMBER is not available in 2000, but the other one should work..did you try it?

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-02-11 : 15:00:24

yes it try,why he wrote min(color)??
(i change A the color to green in line 3)

srouse
Starting Member

7 Posts

Posted - 2010-02-11 : 15:01:57

This works, but I couldn't reference the table variable @t in an exists clause so I loaded the @t into #test.

Scott

declare @t table (
[name] varchar(1), row int, color varchar(10))
insert @t
select 'A' , 1, 'green'
union all select 'A' , 2 , 'green'
union all select 'A' , 3, 'red'
union all select 'B' , 1, 'green'
union all select 'B' , 2, 'green'
union all select 'C', 1, 'red'

--select * from @t

select * into #test from @t

select name, count(*), 'red' as color
from #test
where exists (select 1 from #test z where z.name = #test.name and color = 'red')
group by name

union
select name, count(*), color
from #test
where not exists (select 1 from #test z where z.name = #test.name and color = 'red')
group by name, color

-- clean up
drop table #test
go

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-11 : 15:11:55

quote:
Originally posted by inbs

yes it try,why he wrote min(color)??
(i change A the color to green in line 3)

Well..you did not mention which color needs to be returned if there are more than 1 color for a name and both are NOT red, which is why I have min() there.

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-02-11 : 15:16:15

vijayisonly,you right i have only the red and green.
thank srouse

persiangulf098
Starting Member

10 Posts

Posted - 2010-02-11 : 16:33:49

select name,count(*),max(color)
from test
group by name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources