CODEWITHSUNDEEP
sqlsql-servert-sqlcountaggregate-functions
Chris Holt
Chris Holt

Reputation: 55

Counting Rows Based on Exist Condition

I have researched many Stackoverflow questions that demonstrate how to add conditions to the COUNT statement using a CASE statement but I can't find an example of how to do what I need to. Let's assume I have the following tables:

CREATE TABLE [Project](
    [ProjectId] [int] NOT NULL,
    [ProjectName] [nvarchar](100) NOT NULL
)
GO

CREATE TABLE [ProjectTask](
    [ProjectTaskId] [int] NOT NULL,
    [ProjectId] [int] NOT NULL,
    [TaskName] [nvarchar](100) NOT NULL
)
GO

CREATE TABLE [ProjectTaskOwner](
    [ProjectTaskId] [int] NOT NULL,
    [UserId] [int] NOT NULL
 )
GO

CREATE TABLE [ProjectTaskEntity](
    [ProjectTaskId] [int] NOT NULL,
    [EntityId] [int] NOT NULL,
    [AssignedUserId] [int]
 )
GO

INSERT Project (ProjectId, ProjectName) VALUES (1, 'Test Project')
INSERT ProjectTask (ProjectTaskId, ProjectId, TaskName) VALUES(1, 1, 'Task1')
INSERT ProjectTask (ProjectTaskId, ProjectId, TaskName) VALUES(2, 1, 'Task2')
INSERT ProjectTaskOwner (ProjectTaskId, UserId) VALUES (1, 1)
INSERT ProjectTaskOwner (ProjectTaskId, UserId) VALUES (1, 2)
INSERT ProjectTaskOwner (ProjectTaskId, UserId) VALUES (1, 3)
INSERT ProjectTaskEntity (ProjectTaskId, EntityId, AssignedUserId) VALUES (1, 1, 1)
INSERT ProjectTaskEntity (ProjectTaskId, EntityId, AssignedUserId) VALUES (1, 2, 2)
INSERT ProjectTaskEntity (ProjectTaskId, EntityId, AssignedUserId) VALUES (1, 3, 3)
INSERT ProjectTaskEntity (ProjectTaskId, EntityId, AssignedUserId) VALUES (1, 4, 256)

What I want to do is get for each Project Task the count of entities that are assigned to me and assigned to other task owners. So for the test data above, given a user id of 1 and a project id of 1 I expect:

| TaskId | AssignedToMe | AssignedToOthers |
|   1    |      1       |       2          |
|   2    |      0       |       0          |

So here is what I have so far:

DECLARE @userId INT = 1   
SELECT 
    pt.ProjectId,
    pt.ProjectTaskId,
    COUNT(CASE WHEN pte.AssignedUserId = @userId THEN 1 ELSE NULL END) AS AssignedTo
FROM ProjectTask pt
INNER JOIN Project p
    ON p.ProjectId = pt.ProjectId
LEFT JOIN ProjectTaskEntity pte
    ON pte.ProjectTaskId = pt.ProjectTaskId
WHERE p.ProjectId = 1
GROUP BY pt.ProjectId,  pt.ProjectTaskId

Assigned to me was easy. The problem is I'm not sure how to do others because others isn't just anyone not me. It is anyone not in the ProjectTaskOwner table for this task. Anyone have any idea how to achieve this?

Upvotes: 2

Views: 80

Answers (6)

Chris Holt
Chris Holt

Reputation: 55

Here is the actual query that solved my problem. There are several posts that helped me find this so thanks to everyone for your answers:

DECLARE @userId INT = 1   
SELECT 
    pt.ProjectId,
    pt.ProjectTaskId,
    COUNT(CASE WHEN pte.AssignedUserId = @userId THEN 1 ELSE NULL END) AS AssignedTo,
    COUNT(CASE WHEN eao.OtherUser IS NULL THEN NULL ELSE 1 END) AS AssignedToOther
FROM ProjectTask pt
LEFT JOIN ProjectTaskEntity pte
    ON pte.ProjectTaskId = pt.ProjectTaskId
LEFT JOIN (
    SELECT sPto.ProjectTaskId, sPto.UserId as OtherUser
    FROM ProjectTaskOwner sPto
    WHERE sPto.UserId <> @userId
) eao
    ON  pt.ProjectTaskId = eao.ProjectTaskId AND pte.AssignedUserId = eao.OtherUser
WHERE pt.ProjectId = 1
GROUP BY pt.ProjectId,  pt.ProjectTaskId

Upvotes: 1

Luca C.
Luca C.

Reputation: 56

DECLARE @userId INT = 1   
SELECT 
    pt.ProjectId,
    pt.ProjectTaskId,
    ISNULL(SUM(CASE WHEN pte.AssignedUserId = @userId THEN 1 END),0) AS AssignedToMe,
    ISNULL(SUM(CASE WHEN pte.AssignedUserId <> @userId THEN 1 END),0) AS AssignedToOthers
FROM ProjectTask pt
INNER JOIN Project p
    ON p.ProjectId = pt.ProjectId
LEFT JOIN ProjectTaskOwner pto
    ON pto.ProjectTaskId = pt.ProjectTaskId
LEFT JOIN ProjectTaskEntity pte
    ON pte.ProjectTaskId = pt.ProjectTaskId and
    pte.AssignedUserId = pto.UserId
WHERE p.ProjectId = 1
GROUP BY pt.ProjectId,  pt.ProjectTaskId

http://www.sqlfiddle.com/#!6/58f1e4/28

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

I don't see why you need to project table for this. I would write this as:

SELECT pt.ProjectId, pt.ProjectTaskId,
       SUM(CASE WHEN pte.AssignedUserId = @userId THEN 1 ELSE 0 END) AS AssignedToMe,
       SUM(CASE WHEN pto.UserId IS NULL THEN 1 ELSE 0 END) AS AssignedToOthers
FROM ProjectTask pt LEFT JOIN
     ProjectTaskEntity pte
     ON pte.ProjectTaskId = pt.ProjectTaskId LEFT JOIN
     ProjectTaskOwner pto
     ON pto.ProjectTaskId = pt.ProjectTaskId AND
        pto.UserId = pte.AssignedUserId
WHERE pt.ProjectId = 1
GROUP BY pt.ProjectId, pt.ProjectTaskId;

I replaced the COUNT() with SUM(). As a matter of preference, I prefer SUM() in this situation.

Upvotes: 2

Raging Bull
Raging Bull

Reputation: 18747

Use SUM instead of COUNT:

DECLARE @userId INT = 1   
SELECT pt.ProjectTaskId,
    ISNULL(SUM(CASE WHEN pte.AssignedUserId = @userId THEN 1 END),0) AS AssignedToMe,
    ISNULL(SUM(CASE WHEN pte.AssignedUserId <> @userId THEN 1 END),0) AS AssignedToOthers
FROM ProjectTask pt
INNER JOIN Project p ON p.ProjectId = pt.ProjectId
LEFT JOIN ProjectTaskEntity pte ON pte.ProjectTaskId = pt.ProjectTaskId
WHERE p.ProjectId = 1
GROUP BY  pt.ProjectTaskId

With the data you have provided, it will result:

ProjectTaskId   AssignedToMe    AssignedToOthers
1               1               3
2               0               0

See result in SQL Fiddle.

Upvotes: 0

Nambu14
Nambu14

Reputation: 380

I think this solves the problem. You'll be counting others as not you and anyone not in the ProjectTaskOwner table for this task.

DECLARE @userId INT = 1   
SELECT 
    pt.ProjectId,
    pt.ProjectTaskId,
    COUNT(CASE WHEN pte.AssignedUserId = @userId THEN 1 ELSE NULL END) AS AssignedTo,
    COUNT(CASE WHEN pte.AssignedUserId != @userId AND pte.AssignedUserId != pto.UserId THEN 1 ELSE NULL END) AS AssignedTo
FROM ProjectTask pt
INNER JOIN Project p
    ON p.ProjectId = pt.ProjectId
LEFT JOIN ProjectTaskEntity pte
    ON pte.ProjectTaskId = pt.ProjectTaskId
LEFT JOIN ProjectTaskOwner pto
    on pto.ProjectTaskId = pt.ProjectTaskId
WHERE p.ProjectId = 1
GROUP BY pt.ProjectId,  pt.ProjectTaskId

Upvotes: 0

JeffUK
JeffUK

Reputation: 4241

It's as simple as doing the opposite

DECLARE @userId INT = 1   
SELECT 
    pt.ProjectId,
    pt.ProjectTaskId,
    COUNT(CASE WHEN pte.AssignedUserId = @userId THEN 1 ELSE NULL END) AS AssignedToMe,
    COUNT(CASE WHEN pte.AssignedUserId = @userId THEN null ELSE 0 END) AS AssignedToOthers

FROM ProjectTask pt
INNER JOIN Project p
    ON p.ProjectId = pt.ProjectId
LEFT JOIN ProjectTaskEntity pte
    ON pte.ProjectTaskId = pt.ProjectTaskId
WHERE p.ProjectId = 1
GROUP BY pt.ProjectId,  pt.ProjectTaskId

Upvotes: 0

Related Questions