Hello,

I need to create a stored procedure for total count of the user's. If  User from front end  invites other user to use my tool, that user will be stored into a table name called "test",lets say it will be stored as"Invited 1 User(s)" or if he invites 2 users it will store into table as "Invited 2 User(s)."

But now we have changed the concept to get the ISID (name of the user)  and now when ever the user invites from the front end, the user who have invited should stored in two tables "test" and " test1" table .

After we get the data into test and test1 table i need the total count of a particular user from both tables test and test1.

if i invite a user , the name of the user is getting stored in both test and test1 tables.Now i want to get the count of a user from both tables which should be 1,but its showing 2

.

Reason: Why i am considering the count from 2 tables is because before we were not tracking the usernames and we were storing the count in single test table.,but now we are tracking user names and storing them in both tables(test and test1).

Here is my sample  code:

I need to sum it up to get the total user's from both the table but I should get 1 instead of 2 

SELECT
(select distinct COUNT(*) from dbo.test
where New_Values like  '%invited%'
and Created_By= 'sam'
+
(select distinct count (*)  from dbo.test1
where invited_by ='sam'

Let me know If I am not clear.

Thanks,

sqlDev12

• Edited by SqlDev12 7 hours 28 minutes ago

Table schema and sample data would help in providing assistance.

it will be stored as"Invited 1 User(s)" or if he invites 2 users it will store into table as "Invited 2 User(s)."

Please don't do that if you have the option!! Storing data as a string like this severely limits what you can do with the data later. You never want to back yourself into a corner of substringing and patindexing/charindexing.

Why wouldn't you instead have a table for users and have a column called [invited_by] in which the user id for the person who invited that user is stored. Another option might be to store the invitation too.

DECLARE @Users TABLE (id int IDENTITY, UserName varchar(10), InvitedBy int)
INSERT INTO @Users (UserName, InvitedBy) VALUES ('Fred', null), ('Joe', 1), ('Sue', null), ('John', 1), ('Jill', 3)

SELECT P.UserName, CONCAT('Invited ', COUNT(1), ' User(s)') as InvitationCount
FROM @Users P
INNER JOIN @Users C on (P.id = C.InvitedBy)
GROUP BY P.UserName

I hope you found this post helpful! If you did please vote it as helpful on the left. If it answered your question please mark it as the answer below. :)

• Edited by Daniel Janik 5 hours 18 minutes ago

Thanks Daniel thanks for the reply but I don't  have  an option to join the the two tables as there is common column in those 2 tables.

Can you please suggest any work arounds on this?

Hi SqlDev12,

Your query returns more rows might be due to that 'sam' can invite more than one person. Since you didn't post any details about your tables, based on my assumption, please try below query.

SELECT t.userName,t.CreatedBy,COUNT(1) FROM dbo.text t JOIN dbo.test1 t1 
						 ON t.Created_By=t1.invited_by
						 AND t.userName=t1.userName
GROUP BY t.userName,t.Created_By

If you have any feedback on our support, you can cilck here.

Thanks Daniel thanks for the reply but I don't  have  an option to join the the two tables as there is common column in those 2 tables.

Can you please suggest any work arounds on this?

You do have a join that can be done. Created_by = Invited_By.

My suggestion is that you scrap what you have and start over. The New_Values column shouldn't be structured in a way that you must query it with a leading wildcard (like '%invited%'). Instead this should be an int or perhaps even a bit (1 = invited/ 0 = not invited).

You'll also note that I had a self join on the original query; however, that's just because there was no DDL at the time. There are many ways to make this work but it is going to be difficult for the schema that I assume you have and I would sooner fix all that today then have a headache down the road.