Unique recordcount-CodePudding

I have a SQL table with the following model for an internal Analytics

int UserID
DateTime TimeStamp
int NodeID

Let's say we have the following data.

Record 1 { UserID = 5, DateTime = 28-02-2022 11:00, NodeID = 1 }
Record 2 { UserID = 4, DateTime = 28-02-2022 11:01  NodeID = 1 }
Record 3 { UserID = 4, DateTime = 28-02-2022 11:04  NodeID = 1 }
Record 4 { UserID = 4, DateTime = 28-02-2022 11:07  NodeID = 2 }

To get the total visit pr. node i got

Select NodeID, Count(NodeID)
from Table
Group By NodeID

Which gives

Node 1 Count = 3
Node 2 Count = 1

My question is: How do I in SQL get the unique Node count?

Node 1 Count = 2
Node 2 Count = 1

CodePudding user response：

you use count and group by

to get the number of lines per node

select nodeId node , count(*) row_per_node
from SQLtable
group by nodeID

to get the number of users per node

select nodeId node , count(distinct userid) count_user_node
from SQLtable
group by nodeID

CodePudding user response：

this is close, you can use programming to do the logic

Select NodeID, Count(NodeID) from Table Group By NodeID, UserID