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:

and another like so:

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:

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]