count query help - 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 2000 Forums

SQL Server Development (2000)

count query help

Author

Topic

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2006-10-30 : 11:15:59

Hi all,

I have a table with the following information:

Mytable

Column1 Column2

103 1
103 1
104 NULL
104 NULL
105 1
105 1
105 NULL
105 1
106 0
106 0
106 0
106 NULL

I want the output below:

column2 count
1 2
0 1

I want to show count as 1 even though there are more than one values.How can i achieve this?

Thanks for the help in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-30 : 11:23:47

select column2, count(distinct column1) from mytable group by column2

Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-10-30 : 11:25:47

select sign(count(*))
from ...

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-30 : 20:18:54

quote:

column2 count
1 2
0 1

How do you expect count for 1 to be 2?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-31 : 00:46:55

Madhi, see my query above. Or this enhanced version

select		column2,
		count(distinct column1) [count]
from		mytable
where		column2 is not null
group by	column2
order by	column2 desc

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-31 : 09:16:05

Thanks Peso

Madhivanan

Failing to plan is Planning to fail

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2006-10-31 : 11:27:29

Thanks peso ! i got the results with your query

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources