counting occurrences in a table 1

[svanels]

I have the follow table:

trap survey result
-------------------------------------
t1 120 ok
t2 120 ok
t3 400 plugged
t4 400 cold

etc.

I am interested in a query which returns:

result occurrence
-------------------------------
ok 2
plugged 1
cold 1




etc.

I am using count but I get the results one at a time.

Regards


S. van Els
SAvanEls@cq-link.sr

 

[grega]

How about

select result, count(*)
from table
group by result

Greg.

 

[svanels]

Thank you very much, I knew that had to be something simple S. van Els
SAvanEls@cq-link.sr

 

[svanels]

To elaborate more on this topic:
The results I can classify in a severity (damage) "SevIndex" table

Result Index
------------------------------------
ok 1
out of service 1
other 2
cold 2
plugged 2
rattling 2
hissing 3
leaking 3


I want to make an query to get:

Index total_occurences Survey
-----------------------------------------
1 51 100
2 10 100
3 16 110

The query:

select SevIndex.rate, Inspection.survey, Inspection.result
from SevIndex
inner join inspection
on (inspection.result = sindex.result)
order by rate

works fine, but when I try to put in count & group by the problems like
general sql error & invalid column reference arise.

Any help is appreciated.

Regards
S. van Els
SAvanEls@cq-link.sr

 

[fluteplr]

Why don't you post the code that is giving the error, so we can see what you did. And then post the actual error message.

 

[svanels]

select SevIndex.rate, count(*),Inspection.survey, Inspection.result
from SevIndex
inner join inspection
on (inspection.result = sindex.result)

and

select SevIndex.rate,Inspection.survey, Inspection.result
from SevIndex
inner join inspection
on (inspection.result = sindex.result)
group by rate S. van Els
SAvanEls@cq-link.sr

 

[tlbroadbent]

When you use aggregate functions such as Group By or Count, all columns in the select list must be included in an aggregate function.

Example 1:
[tt]
select
SevIndex.Rate,
count(*) As RecCnt,
Inspection.Survey,
Inspection.Result
from SevIndex
inner join inspection
on (inspection.result = sindex.result)
group by rate
Inspection.survey,
Inspection.result[/tt]

Example 2:
[tt]
select
SevIndex.Rate,
count(*) As RecCnt,
Max(Inspection.survey) As Survey,
Max(Inspection.result) As Result
from SevIndex
inner join inspection
on (inspection.result = sindex.result)
group by rate[/tt] Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[svanels]

Thank you very much all of you!!!! B-) B-) B-) S. van Els
SAvanEls@cq-link.sr