# MS SQL 2005, minimizing of count.

I have some numbers from a table like this:
Number1   Number2    Number3    Number4
01                 02                03              04
And I also have a list of number as a result:
ResultNumber
01
01
02
03
05
07
04

So if I make a count statement of the numbers from the result I will have:
count(01) = 2, count(02) = 1, count(03) = 1, count(04) = 1. --> min(count) = 1
And here is my problem: I want to calculate this value:  (01*02*03*04)*min(count)

How can I get that result from select statement?
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Hi,

I am not clear about the relationship between your two tables, but generally you can do as follows
``````declare @myTable table (ResultNumber varchar(3))
insert @myTable values ('01')
insert @myTable values ('01')
insert @myTable values ('02')
insert @myTable values ('03')
insert @myTable values ('05')
insert @myTable values ('07')
insert @myTable values ('04')

Select Min(SingleCount) From
(
Select ResultNumber, SingleCount=count(*) from @myTable
Group by ResultNumber
) InnerSelect
``````
Commented:
I am sure this is completely the wrong track, but hopefully it will lead you in the right path.
``````declare @myNumbers table (Number1 int, Number2 int, Number3 int, Number4 int)
insert @myNumbers values (1,2,3,4)
declare @myTable table (ResultNumber int)
insert @myTable values ('01')
insert @myTable values ('01')
insert @myTable values ('02')
insert @myTable values ('03')
insert @myTable values ('05')
insert @myTable values ('07')
insert @myTable values ('04')

--(01*02*03*04)*min(count)
Select (Number1 * Number2 * Number3 * Number4) * MinFromCounts FROM
(
Select MinFromCounts = Min(SingleCount) From
(
Select ResultNumber, SingleCount=count(*) from @myTable
Group by ResultNumber
) InnerSelect
) InnerSelect2
CROSS JOIN @myNumbers N
``````

Experts Exchange Solution brought to you by