Count (*)

Question

Post reply

Count (*)

tran008

SSC Eights!

Points: 952
More actions
July 29, 2003 at 11:45 am

#160535

Hi all,

I have a table, and want to figure how I can return the count values that are greater than 5, and count value that are less then 5 in the table column:

eg...

table1 columns

1

1

2

2

3

4

7

8

8

---------

result:

count 6, 3

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

jpipes SSChampion Points: 12230 More actions · Answer 1

jpipes

SSChampion

Points: 12230

July 29, 2003 at 11:48 am

#467553



SELECT column, COUNT(*) 

FROM table1 

WHERE column > 5

GROUP BY column

HAVING COUNT(*) < 5

I think this is what you meant...

brendt hess Hall of Fame Points: 3256 More actions · Answer 2

The way I prefer for this is to use a CASE statement, e.g.:

SELECT Sum(Case When Column > 5 Then 1 Else 0 End) As GreaterThan5,

Sum(Case When Column < 5 Then 1 Else 0 End) As LessThan5

FROM MyTable

You could also do it as two nested select statements:

SELECT (Select Count(*) From Table1 Where Column > 5) As GreaterThan5, (Select Count(*) From Table1 Where Column > <) As LessThan5

jpipes SSChampion Points: 12230 More actions · Answer 3

quote:

The way I prefer for this is to use a CASE statement, e.g.:

SELECT Sum(Case When Column > 5 Then 1 Else 0 End) As GreaterThan5,

Sum(Case When Column < 5 Then 1 Else 0 End) As LessThan5

FROM MyTable

You could also do it as two nested select statements:

SELECT (Select Count(*) From Table1 Where Column > 5) As GreaterThan5, (Select Count(*) From Table1 Where Column > 5) As LessThan5

Yep, I misread the question as with values greater than 5 and less than total occurrences. Silly me. Good job brendthess...