SQL Server: update + count for each columns

user_15942739 0
Viewed 70

I'm trying to update my database. My USER table has a column NbFollower that should hold the number of people who follow this user.

So I need to count that number from another table FOLLOWING_USER.

The USER.UserID = 1 appears 4 times in FOLLOWING_USER. That means he has 4 followers.

How can I use the function UPDATE and a COUNT at the same time, for each people ?

UPDATE [USER]
SET NbFollower = (SELECT count(User)
                  FROM FOLLOWING_USER FU
                  INNER JOIN [USER] U ON FU.[User] = U.UserID)

This returns "13" for ALL my users (13 is the total number of rows in FOLLOWING_USER)

So I need something like this, but automatised for all my users:

SELECT COUNT([User]) 
FROM FOLLOWING_USER 
WHERE [User] = 1

EDIT: here my table USER

CREATE TABLE [USER]
(
    UserID int IDENTITY(1, 1) PRIMARY KEY,
    URL varchar(50) NOT NULL,
    Name varchar(20) NOT NULL,
    Password varchar(50) NOT NULL,
    Email varchar(100) NOT NULL,
    Age int NOT NULL,
    Gender char(1) NOT NULL,
    Location varchar(50) NOT NULL,
    Description varchar(200) NULL,
    NbFollower int NULL DEFAULT 0,
    Rating int NULL DEFAULT 0
);

ALTER TABLE [USER]
    ADD ShareoutwardzID INT 
        FOREIGN KEY REFERENCES SHAREOUTWARDZ(ShareoutwardzID),
        CONSTRAINT Age CHECK (Age>=18);

INSERT INTO [USER] (Name, URL, Password, Email, Age, Gender, Location, Description, Rating)
VALUES
('lily', '/NAME', 'azertyuop123', 'lily@hotmail.com', 21, 'F', 'USA', NULL, 4);

Here my table FOLLOWING_USER:

CREATE TABLE FOLLOWING_USER
(
    [User] int NOT NULL FOREIGN KEY REFERENCES [USER](UserID),
    Follower int NOT NULL FOREIGN KEY REFERENCES FOLLOWER(UserID),
);

INSERT INTO FOLLOWING_USER([User], Follower)
VALUES (1, 9), (2, 9), (4, 9), (6, 9);
tsql sql-server sql
2 Answers

You were almost there you can user the [user].user_id to xount only the correct number.

And you have to use COUNT(*)

CREATE tABLE SHAREOUTWARDZ (ShareoutwardzID int PRIMARY KEY)
GO

CREATE TABLE [USER]
(
    UserID int IDENTITY(1, 1) PRIMARY KEY,
    URL varchar(50) NOT NULL,
    Name varchar(20) NOT NULL,
    Password varchar(50) NOT NULL,
    Email varchar(100) NOT NULL,
    Age int NOT NULL,
    Gender char(1) NOT NULL,
    Location varchar(50) NOT NULL,
    Description varchar(200) NULL,
    NbFollower int NULL DEFAULT 0,
    Rating int NULL DEFAULT 0
);
ALTER TABLE [USER]
    ADD ShareoutwardzID int FOREIGN KEY REFERENCES SHAREOUTWARDZ(ShareoutwardzID),
    CONSTRAINT Age CHECK (Age>=18);
INSERT INTO [USER](Name, URL, Password, Email, Age, Gender, Location, Description, Rating)
VALUES
('lily', '/NAME', 'azertyuop123', 'lily@hotmail.com', 21, 'F', 'USA', NULL, 4),
('MArk', '/NAME', 'azertyuop123', 'mark@hotmail.com', 21, 'M', 'USA', NULL, 4),
('Mary', '/NAME', 'azertyuop123', 'Mary@hotmail.com', 21, 'F', 'USA', NULL, 4),
('David', '/NAME', 'azertyuop123', 'david@hotmail.com', 21, 'M', 'USA', NULL, 4)
;
GO

CREATE TABLE FOLLOWER(UserID int PRIMARY KEY);
INSERT FOLLOWER VALUES (9)
GO

CREATE TABLE FOLLOWING_USER
(
    [User] int NOT NULL FOREIGN KEY REFERENCES [USER](UserID),
    Follower int NOT NULL FOREIGN KEY REFERENCES FOLLOWER(UserID),
);
INSERT INTO FOLLOWING_USER([User], Follower)
VALUES
(1, 9),
(2, 9),
(2, 9),
(3, 9);
GO

    UPDATE [USER]
SET NbFollower = (SELECT count(*)
                  FROM FOLLOWING_USER FU
                  
                  WHERE FU.[User] = [USER].UserID)
GO

SELECT * FROM [USER]
GO

UserID | URL   | Name  | Password     | Email             | Age | Gender | Location | Description | NbFollower | Rating | ShareoutwardzID
-----: | :---- | :---- | :----------- | :---------------- | --: | :----- | :------- | :---------- | ---------: | -----: | --------------:
     1 | /NAME | lily  | azertyuop123 | lily@hotmail.com  |  21 | F      | USA      | null        |          1 |      4 |            null
     2 | /NAME | MArk  | azertyuop123 | mark@hotmail.com  |  21 | M      | USA      | null        |          2 |      4 |            null
     3 | /NAME | Mary  | azertyuop123 | Mary@hotmail.com  |  21 | F      | USA      | null        |          1 |      4 |            null
     4 | /NAME | David | azertyuop123 | david@hotmail.com |  21 | M      | USA      | null        |          0 |      4 |            null

db<>fiddle here

user_51935360 
UPDATE [USER]
SET NbFollower = UserCount.TotalCount
                 FROM [USER]
                 INNER JOIN (SELECT COUNT(User) TotalCount, User 
                             FROM [Following_User] GROUP BY User) As UserCount
                 ON UserCount.User = [USER].UserId
user_114983280
Related