'Unique recordcount

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
sql


Solution 1:[1]

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

Solution 2:[2]

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

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Segun Adeniji

Related Questions