CODEWITHSUNDEEP
sql-server
Hassan Alrawashdeh
Hassan Alrawashdeh

Reputation: 65

Get count even if the condition doesn't apply

I have two tables

1) Document: which represent a document

+----+----------+------+
| ID | Body     | Type |
+----+----------+------+
|  1 | Ramesh   |  1   |
|  2 | Khilan   |  1   |
|  3 | kaushik  |  4   |
|  4 | Chaitali |  2   |
|  5 | Hardik   |  2   |
+----+----------+------+

2) Destination: which represent a party of the document

+--------+------------+--------+
| UserId | DocumentId | Status |
+--------+------------+--------+
|   6    |      3     |    4   |
|   4    |      5     |    5   |
|   89   |      2     |    0   |
|   15   |      4     |    3   |
|   89   |      1     |    0   |
+--------+------------+--------+

The status column represent a folder for the user, i want to get the count for each type for each folder, even if the folder is empty for a specifi user, however if want them in this from,

+--------+--------+--------------+--------------+--------------+
| UserId | Status | Type 1 Count | Type 2 Count | Type 4 Count |
+--------+--------+--------------+--------------+--------------+
|   89   |    0   |      2       |      0       |      0       |
|   89   |    3   |      0       |      0       |      0       |
|   89   |    4   |      0       |      0       |      0       |
|   89   |    5   |      0       |      0       |      0       |
+--------+--------+--------------+--------------+--------------+

the issue I'm facing is I can't find a way to get the types the user does not have by join, i can get them using CASE but not in the form i want

my query is:

`SELECT dd.[Status],  
    SUM(CASE WHEN d.[Type] = 1 THEN 1 ELSE 0 END) AS 'Type1Count'  
    SUM(CASE WHEN d.[Type] = 2 THEN 1 ELSE 0 END) AS 'Type2Count'  
    SUM(CASE WHEN d.[Type] = 4 THEN 1 ELSE 0 END) AS 'Type4Count'  
 FROM [User] u LEFT JOIN [Destination] dd ON u.[Id] = dd.[UserId]  
    LEFT JOIN [Document] d ON dd.[DocumentId] = d.[Id]  
 WHERE u.[Id] = @UserId`

the result is 

+--------+--------+--------------+--------------+--------------+
| UserId | Status | Type 1 Count | Type 2 Count | Type 4 Count |
+--------+--------+--------------+--------------+--------------+
|   89   |    0   |      2       |      0       |      0       |
+--------+--------+--------------+--------------+--------------+

Upvotes: 3

Views: 76

Answers (1)

strickt01
strickt01

Reputation: 4048

So join all users onto a table of all statuses (I have named this Folder as per you description in the question) before you then join to Document and Destination:

SELECT u.UserId, st.Status, 
SUM(CASE WHEN doc.Type = 1 THEN 1 ELSE 0 END) AS [Type 1 Count],
SUM(CASE WHEN doc.Type = 2 THEN 1 ELSE 0 END) AS [Type 2 Count], 
SUM(CASE WHEN doc.Type = 4 THEN 1 ELSE 0 END) AS [Type 4 Count]

FROM User u

CROSS JOIN Folder st

LEFT OUTER JOIN Destination d
ON d.UserId = u.UserId
AND d.Status = st.Status

LEFT OUTER JOIN Document doc
ON doc.ID = d.DocumentId

GROUP BY u.UserId, st.Status

ORDER BY u.UserId

Upvotes: 1

Related Questions