SELECT MULTIPLE COUNT PER RECORD

[ba543]

I have a table that has NODE_ID, NODE_NAME, IP_Address, EVENT_TYPE, EVENT_Name FROM the NODE_Events table. Each of these nodes has several events that happen. for example Event_Type = 5 is Node Down or Event_Type = 1 is Node UP. So there may be several of these per node and I want to count those for each type of event in a single row heres the data example to clarify.

Node_ID Node_Name IP_Address Event_Type Event_Name
1 FPSE 1.1.1.1 1 Node_UP
1 FPSE 1.1.1.1 1 Node_UP
1 FPSE 1.1.1.1 5 Node_Down
1 FPSE 1.1.1.1 5 Node_Down
5 FPNE 1.2.1.1 10 Router Down
5 FPNE 1.2.1.1 3 Switch Up


So theres the data now if I want to count for Event 1,5,10,3 for all the Nodes then it would look like this

Node_ID Node_Name IP_Address ET_1 ET_5 ET_10 ET_3
1 FPSE 1.1.1.1 2 2 0 0
5 FPNE 1.2.1.1 0 0 1 1

So with this we can see each count for each type of event for that Node. Any help would be appreciated.

 

[gmmastros]

Try this...

Select Node_Id,
       Node_Name,
       IP_Address,
       Sum(Case When Event_Type = 1 Then 1 Else 0 End) As ET_1,
       Sum(Case When Event_Type = 5 Then 1 Else 0 End) As ET_5,
       Sum(Case When Event_Type = 10 Then 1 Else 0 End) As ET_10,
       Sum(Case When Event_Type = 3 Then 1 Else 0 End) As ET_3
From   NODE_Events
Group By Node_Id, Node_Name, IP_Address

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom