sqlsql-serverscada

How can I write a SQL query to list distinct values and their number of occurrences from a table?


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;