I have seem similar questions on here but none of the answers seem to fit what I am looking for-
Say I have one table like so:
AssignedTo | DeptID | Type |
---|---|---|
Mary | 5001 | Elementary |
Bob | 5002 | Middle |
Bob | 5003 | Elementary |
Jill | 5004 | High |
Bob | 5005 | High |
Bob | 5006 | High |
and another like so:
User | Telephone | Address |
---|---|---|
Mary | 111-222-3333 | 111 South Lane |
Bob | 222-111-3333 | 222 North Drive |
Jill | 333-222-1111 | 555 Highway |
I want to output the second table with a left join on the first table with the total number of 'Type' (elementary, middle,high) by each assigned user, so it would like something like:
User | Telephone | Total Elementary | Total Middle | Total High |
---|---|---|---|---|
Mary | 111-222-3333 | 1 | 0 | 0 |
Bob | 222-111-3333 | 1 | 1 | 2 |
Jill | 333-222-1111 | 0 | 0 | 1 |
I've tried --Count(case when <table>.[type] = 'Elementary' then 1 else 0 end) AS ElementaryCount,
but this just gets me the entire table, not by the user on the left join.
Anyone have any ideas how I can go about this?
CodePudding user response:
This is basically a pivot which you can do using conditional aggregation via an outer apply.
Given tables a
and u
,
select [User], Telephone,
IsNull(a.e, 0) Total_Elementary,
IsNull(a.m, 0) Total_Middle,
IsNull(a.h, 0) Total_High
from u
outer apply (
select
Sum(case when type = 'Elementary' then 1 end) e,
Sum(case when type = 'Middle' then 1 end) m,
Sum(case when type = 'High' then 1 end) h
from a
where a.AssignedTo = u.[User]
)a;
CodePudding user response:
You should have posted your full attempted script. But even without it, I think you want something like this. I use a derived table to calculate the counts and then join that back to the user table.
SELECT
t2.[User]
, t2.Telephone
, ISNULL(t2.TotalElementary,0) as [Total Elementary]
, ISNULL(t2.TotalMiddle,0) as [Total Middle]
, ISNULL(t2.TotalHigh,0) as [Total High]
FROM Table2 as t2
LEFT OUTER JOIN (
SELECT t1.AssignedTo
, COUNT(CASE WHEN t1.[Type] = 'Elemetary' THEN 1 ELSE 0 END) as TotalElementary
, COUNT(CASE WHEN t1.[Type] = 'Middle' THEN 1 ELSE 0 END) as TotalMiddle
, COUNT(CASE WHEN t1.[Type] = 'High' THEN 1 ELSE 0 END) as TotalHigh
FROM Table1 as t1
GROUP BY t1.AssignedTo
) as CountsTable as ct
ON ct.AssignedTo = t2.[User]