Using COUNT and DISTINCT in the same SQL Server query

Question

I have an SQL Server table Cascading with following structure:

+----+--------+----------------+           +----------------+-----------+  
| ID | NodeID | ActivationTime |           | ActivationTime | NodeCount |  
+----+--------+----------------+           +----------------+-----------+  
| 1  | 448    | 1              |           | 1              | 1         |  
| 2  | 195    | 2              |           | 2              | 3         |  
| 3  | 504    | 2              |           | 3              | 6         |  
| 4  | 609    | 2              |           | ...            | ...       |  
| 5  | 15     | 3              |           | ...            | ...       |  
| 6  | 31     | 3              |    =>     | ...            | ...       |  
| 7  | 56     | 3              |           | ...            | ...       |  
| 8  | 461    | 3              |           | ...            | ...       |  
| 9  | 585    | 3              |           | ...            | ...       |  
| 10 | 345    | 3              |           | ...            | ...       |  
| .. | ...    | ...            |           | ...            | ...       |  
+----+--------+----------------+           +----------------+-----------+

I need to calculate the number of times NodeID is appearing in the table with DISTINCT ActivationTime. For instance, 1 node at ActivationTime 1, 3 nodes at ActivationTime 2, 6 nodes at ActivationTime 3 and so on. The desired result is shown at the right side.

I tried the following unsuccessful query:

SELECT DISTINCT(ActivationTime) FROM CASCADING WHERE ActivationTIme IN
(SELECT DISTINCT(COUNT(ActivationTime)) AS NodeCount FROM CASCADING
GROUP BY ActivationTime)

DVT · Accepted Answer · 2017-08-01 18:23:43Z

2

Use COUNT(DISTINCT NodeID) and GROUP BY ActivationTime

SELECT ActivationTime, COUNT(DISTINCT NodeID) NumberOfID
FROM
  CASCADING
GROUP BY ActivationTime

edited Aug 1, 2017 at 18:23

answered Aug 1, 2017 at 18:22

DVT

3,1274 gold badges16 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lamak Over a year ago

COUNT(DISTINCT NodeID)

DVT Over a year ago

Yes, I fixed it.

Nilesh Patel · Accepted Answer · 2017-08-01 18:26:22Z

0

SELECT COUNT(NodeCount) AS Count, ActivationTime FROM CASCADING GROUP BY ActivationTime

answered Aug 1, 2017 at 18:26

Nilesh Patel

113 bronze badges

Comments

Evelyn Ma · Accepted Answer · 2017-08-01 18:32:58Z

0

It works for me by following:

select activationtime, count(*) from cascading group by activationtime;

answered Aug 1, 2017 at 18:32

Evelyn Ma

4942 silver badges4 bronze badges

Collectives™ on Stack Overflow

Using COUNT and DISTINCT in the same SQL Server query

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related