Get count even if the condition doesn't apply

Question

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       |
+--------+--------+--------------+--------------+--------------+

What do your queries look like? What do your results look like? — negacao, Dec 22 '16 at 15:05
Off-topic: Why do you keep reverting the edits that format your tables and give your question a better, more readable look? — Josh Part, Dec 22 '16 at 15:19
I See 2 issues) 1 you need to Make up data for each user so that they have each type associated to them a cross join to a distinct list of types shoudl do it. 2) you may need a dynamic pivot. if there are types beyond 1,2,and 4. I direct you to an older post showing how to do a dynamic pivot: http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — xQbert, Dec 22 '16 at 16:03

strickt01 · Accepted Answer · 2016-12-26T17:59:12.173

1

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

edited Dec 26 '16 at 17:59

answered Dec 22 '16 at 15:10

strickt01

3,959
1
17
32

it gave me the same result as my query, which didn't help – Hassan Alrawashdeh Dec 22 '16 at 15:20
I see the issue - it wasn't immediately obvious in the first revision of the question. I've amended my answer accordingly. – strickt01 Dec 22 '16 at 15:29
@strickt01 Your `inner join (Select...) on 1=1` is really just a `cross join`... why not call it that? you would eliminate the 1=1. The "Problem is not all users have all statues, so by generating a cross join to users for all possible status, you have a record for every user status and then can count for the document/destination. Which is what you've done via a cross join – xQbert Dec 22 '16 at 16:00
A fair point! The result is the same but syntactically I admit the `CROSS JOIN` is cleaner... – strickt01 Dec 22 '16 at 16:04
You'll need in that case to create a table of valid statuses (either temporary or permanent) and then `CROSS JOIN` to this rather than to `SELECT DISTINCT Status FROM Destination`. Answer amended accordingly. – strickt01 Dec 26 '16 at 17:57

Get count even if the condition doesn't apply

1 Answers1