I have a table that contains a history of alarm events.
I think the only pertinent column for this is Message, but there are others such as Time/Date and Source.
Here’s a sample table:
| Time/Date | Message | Source |
|---|---|---|
| 2022-04-27/11:59:28 | Code 1 | VFD1 |
| 2022-04-27/11:59:37 | Code 4 | VFD1 |
| 2022-04-27/11:59:39 | Code 1 | VFD1 |
| 2022-04-27/11:59:42 | Code2 | VFD1 |
| 2022-04-27/11:59:44 | Code 1 | VFD1 |
| 2022-04-27/11:59:46 | Code 3 | VFD1 |
| 2022-04-27/11:59:48 | Code 1 | VFD1 |
| 2022-04-27/11:59:50 | Code 2 | VFD1 |
From this, I’d like to create something like this:
| Message | Occurrences |
|---|---|
| Code 1 | 4 |
| Code 2 | 2 |
| Code 3 | 1 |
| Code 4 | 1 |
This is being done inside a SCADA software package (ICONICS/Genesis64), so I’m not sure of the exact flavor of SQL, but I think it should be Microsoft SQL Server or similar to it.
I can run this:
SELECT COUNT( DISTINCT Message) as Messages FROM dm_Alarms
to get how many unique values I have, but I’m stuck on how to count for each unique value, and then list them.
And I do NOT know what all values I will possibly have for Message, it could be very many and change over time.
Thank You
>Solution :
It appears you just need to aggregate?
select Message, count(*) Occurrences
from dm_Alarms
group by Message;