tsql - How to find number of subscribers per question?

Question

I am trying to find the number of subscriber per question. The challenge here is that users are subscribing more then once, but I only need to count when that user subscribe the first time. I am still learning please if someone can direct me. I have attached a link the table and code I have used

select * , COUNT(GroupQuestionSubscriberId) NumberOfSubscriber
from [dbo].[ADF_GroupQuestionSubscribers]
where GroupQuestionId = 4654
GROUP BY [GroupQuestionSubscriberId], [GroupQuestionId], [UserID], [Status], [CreatedDt]

question from:https://stackoverflow.com/questions/65890768/how-to-find-number-of-subscribers-per-question

Answer 1

The column CreatedDt is very likely to contain unique values for every row of data. Including that column in your group by clause will produce many counts that equal 1. In order for your current query to produce a count of 2 you need two rows with exactly the same GroupQuestionSubscriberId, GroupQuestionId, UserID, Status and CreatedDt. If all this is not many a lot of sense, then I suggest you brush up on your group by knowledge by reading the documention.

If the intention is to display that count next to the available data, then using a cross apply is a possible solution.

Sample data

create table ADF_GroupQuestionSubscribers
(
  GroupQuestionSubscriberId int,
  GroupQuestionId int,
  UserId int,
  Status nvarchar(10),
  CreatedDt datetime
);

insert into ADF_GroupQuestionSubscribers (GroupQuestionSubscriberId, GroupQuestionId, UserId, Status, CreatedDt) values
(8031, 4654, 32338, 'Subscribed', '2021-01-19 15:41:25.970'),
(8035, 4654, 32299, 'Subscribed', '2021-01-19 15:55:14.123'),
(8038, 4654, 32338, 'Subscribed', '2021-01-19 16:00:27.343'),
(8094, 4654, 32466, 'Subscribed', '2021-01-20 06:42:50.323');

Solution

select gqs.*,
       c.NumberOfSubscribers
from ADF_GroupQuestionSubscribers gqs
cross apply ( select count(distinct gqsc.UserId) as NumberOfSubscribers
              from ADF_GroupQuestionSubscribers gqsc
              where gqsc.GroupQuestionId = gqs.GroupQuestionId ) c
where gqs.GroupQuestionId = 4654;

Result

GroupQuestionSubscriberId  GroupQuestionId  UserId  Status      CreatedDt                NumberOfSubscribers
-------------------------  ---------------  ------  ----------  -----------------------  -------------------
8031                       4654             32338   Subscribed  2021-01-19 15:41:25.970  3
8035                       4654             32299   Subscribed  2021-01-19 15:55:14.123  3
8038                       4654             32338   Subscribed  2021-01-19 16:00:27.343  3
8094                       4654             32466   Subscribed  2021-01-20 06:42:50.323  3

Fiddle to see things in action.

Edit: solution updated with new expected result information.