Min/ Max from subquery containing count.

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

Transact-SQL (2000)

Min/ Max from subquery containing count.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-05-11 : 08:22:08

kees writes "My data is simple:
Table name = cards
2 columns Cardno ( a number) and a date.

Now I want to see how many times a card is used.
I do:
select count(1) from cards
group by cardno
order by 1

No problem there.

What I do need to know are the min and max values of these counts.
That data is in the first and last row of the result set but this table can have a lot of entries.
I just want to know that all cards were at least used 4 times (min value) and at least 1 card was used 10 times( max value).

I don't even need to know which cards.

Somehow MS SQL doesn't like :
select max( count(1) ) from ..."

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-11 : 08:28:29

Somthing like this ..

select CardNo, count(1) from cards
group by cardno
order by 2
HAving (Count(1) = 4) or (Count(1) = 10)

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-11 : 09:45:46

quote:
Originally posted by chiragkhabaria

Somthing like this ..

select CardNo, count(1) from cards
group by cardno
order by 2
HAving (Count(1) = 4) or (Count(1) = 10)

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Order by should be the last part

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-11 : 12:17:03

quote:
Originally posted by madhivanan

quote:
Originally posted by chiragkhabaria

Somthing like this ..

select CardNo, count(1) from cards
group by cardno
order by 2
HAving (Count(1) = 4) or (Count(1) = 10)

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Order by should be the last part

Madhivanan

Failing to plan is Planning to fail

Thanks for correcting

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

kapilarya
Yak Posting Veteran

86 Posts

Posted - 2006-05-11 : 14:04:09

I think what he requires this
if (select count(1) from cards group by cardno having count(1) =10)
CardNo, count(1) from cards
group by cardno
HAving (Count(1) = 4)
order by 2

Kapil Arya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources