Can I Select DISTINCT on 2 columns and Sum grouped by 1 column in one query?

Is it possible to write one query, where I would group by 2 columns in a table to get the count of total members plus get a sum of one column in that same table, but grouped by one column?

For example, the data looks like this

I want to get a count on distinct combinations of columns “OHID” and “MemID” and get the SUM of the “Amount” column grouped by OHID. The result is supposed to look like this

I was able to get the count correct using this query below

SELECT count(*) as TotCount
from (Select DISTINCT OHID, MemID
      from #temp) AS TotMembers

However, when I try to use this query below to get all the results together, I am getting a count of 15 and a totally different total sum.

SELECT t.OHID,
       count(TotMembers.MemID) as TotCount,
       sum(t.Amount) as TotalAmount
from (Select DISTINCT OHID, MemID
      from #temp) AS TotMembers
join #temp t on t.OHID =  TotMembers .OHID
GROUP by t.OHID

Answer

If I understand correctly, you want to consider NULL as a valid value. The rest is just aggregation:

select t.ohid,
       (count(distinct t.memid) +
        (case when count(*) <> count(t.memid) then 1 else 0 end)
       ) as num_memid,
       sum(t.amount) as total_amount
from #temp t
group by t.ohid,

The case logic might be a bit off-putting. It is just adding 1 if any values are NULL.

You might find this easier to follow with two levels of aggregation:

select t.ohid, count(*), sum(amount)
from (select t.ohid, t.memid, sum(t.amount) as amount
      from #temp t
      group by t.ohid, t.memid
     ) t
group by t.ohid