-1

I have a SQL Server database with the following structure

CREATE TABLE users (
userid INT IDENTITY PRIMARY KEY CLUSTERED, 
age INT
)
GO
CREATE TABLE userstatus(
userstatusID INT IDENTITY PRIMARY KEY CLUSTERED, 
statusid INT, 
userid INT FOREIGN KEY REFERENCES dbo.users(userid)
)
GO

Some sample data:

INSERT  INTO dbo.users
        ( age )
VALUES  ( 15  -- age - int
          ),
        ( 20 ),
        ( 25 );

INSERT  INTO dbo.userstatus
        ( statusid, userid )
VALUES  ( 1, 1 ),
        ( 1, 2 ),
        ( 1, 3 ),
        ( 2, 1 ),
        ( 3, 1 ),
        ( 2, 2 );

Now I want to get the sum of user ages, and the sum of user ages that has status=2.

Something like:

Age with status = 2:  35
Total Ages:           60
Improve this question
2
  • 3
    You should present all the details of your question at the outset. Incrementally adding details to your question frustrates the process of providing answers.
    – Hannah Vernon ♦
    Nov 8, 2017 at 15:33
  • Absolutely agree with you, but I remember about the details after the provided answer. So I am sorry for that.
    – Artashes Khachatryan
    Nov 8, 2017 at 15:35

1 Answer 1

Reset to default
6

This seems quite straightforward:

SELECT  SUM(CASE WHEN us.userid IS NOT NULL THEN u.age END) FilteredAge,
        SUM(u.age) TotalAge
FROM dbo.users u
LEFT JOIN dbo.userstatus us
    ON u.userid = us.userid
    AND us.statusid = 2
;

Updated:

In the case of the same user having multiple same status in the dbo.userstatus table, you can use:

SELECT  SUM(CASE WHEN us.userid IS NOT NULL THEN u.age END) FilteredAge,
        SUM(u.age) TotalAge
FROM dbo.users u
LEFT JOIN ( SELECT DISTINCT userid
            FROM dbo.userstatus 
            WHERE statusid = 2) us
    ON u.userid = us.userid
;
Improve this answer
3
  • thanks for help. I have updated my question one more time. So know I have 3 tables, and I want to have the sum of ages based on location also.
    – Artashes Khachatryan
    Nov 8, 2017 at 15:26
  • 3
    No, please do NOT update the question one more time. Your questions has already been answered. You can mark an answer as accpted. If you have additional problems, post another question.
    – ypercubeᵀᴹ
    Nov 8, 2017 at 15:44
  • 1
    @ArtashesKhachatryan - Since a moderator closed your other question as a duplicate, stating this answer was close enough to what you wanted that you should be able to get there from here, why don't you (the OP, not this answerer) try making the necessary modifications? If you run into problems, then perhaps posting the situation and the query you're working with that's giving you a problem would be acceptable as a new question. This would also give you the chance to remember all the issues you need to deal with, and incorporate everything at once.
    – RDFozz
    Nov 8, 2017 at 17:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.