Frequency Query

on a SQL 7.0 database is it possible to do a query to analyze frequency of the entries in the database?in other words I want to know which entries have the most instances.

Hi,

You can use the count method in combination with a group by clause.
For example: SELECT ProductName, COUNT(*) FROM Orders GROUP BY ProductName

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

thanks for your reply, the column name in the table is Tagname, would it be possible to count all entries for a specific time period? alarmlog is the table name, eventstamp is the time stamp of the entry.

like this:

select *, count(*)

from alarmlog

where eventstamp > '02/01/2007 00:00:00' and

eventstamp < '02/02/2007 00:00:00

thanks

Gary

|||

Yes but you should do it like this:

select TagName, count(*)
from alarmlog
where eventstamp > '02/01/2007 00:00:00' and eventstamp < '02/02/2007 00:00:00'
group by TagName

FYI: This is a good site with basic SQL syntax: http://www.w3schools.com/sql/default.asp

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

thanks again.

Gary

|||

Sorry one more quick question, for the results can I add another column to appear? there is a comment column for each entry that has the description in it that would be useful in the resluts.

Gary

|||

You can add as much columns as you want but the column must be included in the group by clause OR included in an aggregate function like (SUM, AVG, MIN, MAX, COUNT, ...)

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog