-2

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

PenMonk
  • 31
  • 4

1 Answers1

1

It appears you just need to aggregate?

select Message, count(*) Occurrences
from dm_Alarms
group by Message;
Stu
  • 30,392
  • 6
  • 14
  • 33
  • Thank you, that gets me where I needed to go. Clearly have a lot to learn still. I'm not sure if using count(*) vs count(Message) matters, I notice that the first includes null Message values, and the latter does not. – PenMonk Apr 27 '22 at 16:40
  • Yes count(*) counts all rows, count(column) will ignore nulls - use whichever gives the desired results. – Stu Apr 27 '22 at 16:49