COUNTIF Equivalent

[Savil]

Hi
I have a table with 60 columns of integer data. I need to be able to count the number of columns where the values are between 1 and 12, 13 and 24, 25 and 36.......

What i'm actually looking for is an equivalent to Excels COUNTIF function.

Any Ideas.
Savil

 

[Katy44]

you haven't mentioned any field names, but you need something like :

select count(*)
from <tablename>
where <fieldname> between 1 and 12

Has this helped?

 

[dbomrrsm]

you may be helped by the CASE statement here - look at it in BOL.

[blue]DBomrrsm[/blue]

 

[Savil]

That does'nt quite work, the field names are billingOrder1 thru to 60

eg
billingOrder1 = 6
billingOrder2 = 9
billingOrder2 = 12
billingOrder2 = 32
billingOrder2 = 35
billingOrder2 = 0
billingOrder2 = 17
billingOrder2 = 48
billingOrder2 = 44
billingOrder2 = 27
...

So the answer is
1 and 12 = 3
13 and 24 = 1
25 and 36 = 1
37 and 48 = 2
49 and 60 = 0

Savil

 

[Savil]

SORRY Cut and paste does bite

That does'nt quite work, the field names are billingOrder1 thru to 60

eg
billingOrder1 = 6
billingOrder2 = 9
billingOrder3 = 12
billingOrder4 = 32
billingOrder5 = 35
billingOrder6 = 0
billingOrder7 = 17
billingOrder8 = 48
billingOrder9 = 44
billingOrder10 = 27
...

So the answer is
1 and 12 = 3
13 and 24 = 1
25 and 36 = 1
37 and 48 = 2
49 and 60 = 0

Savil

 

[vongrunt]

Short answer would be: SQL database ain't bunch of Excel sheets

 

[SQLScholar]

You might be able to do something with a cross tab. I am no expert - but

http://www.tek-tips.com/viewthread.cfm?qid=900713

helped me.

Good Luck

Dan

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[SQLSister]

And these are each in differnt fields?

First your database structure is seriously flawed, you will never be able to properly query or maintain this database with such a poor structure. You should have a related table for this information not separate fields.

However, I recognize you may not have designed it and may be stuck with the structure. How to solve the problem then?

YOu could create a union statement to get the proper infor from each table into a set of fields. Then use it as a derived table and sum the fields.


Questions about posting. See faq183-874