July 29, 2003 at 11:45 am
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
July 29, 2003 at 11:48 am
SELECT column, COUNT(*)
FROM table1
WHERE column > 5
GROUP BY column
HAVING COUNT(*) < 5
I think this is what you meant...
July 29, 2003 at 12:04 pm
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
July 29, 2003 at 12:24 pm
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...
July 29, 2003 at 12:31 pm
thanks for all your help....
July 30, 2003 at 3:30 am
select (select count(*) from Table_Name where Column_Name<5) as 'LESS',
(select count(*) from Table_Name where Column_Name>5) as 'MORE'
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply