tSQL aggregate functions and group bys

Question

So, I can't seem to find a way to get this to work. But, what I need is as follow.

I have a table that has lets say types.

Type_ID, Type_Description

Then I have a table of Items. [Item Type is fk to type table]

Item_ID, Item_Type

Then I have a Results Table. [Item_ID is fk to Item table]

Result_ID, Item_ID, Cost

So what i am needing for output is Grouped by the Type_ID - The Count of Items(can be 0), - The Count of Results(Can be 0), - and the sum of Cost(can be 0)

I dont have direct access to these tables. I am having to build the sql and send it to an api so I dont get to know the error simply the results if successful and error 500 if not.

Seems to be older tSQL. As List and STRING_AGG dont seem to be available.

EDIT: As requested - Sample Data

 +---------+------------------+ 
 | Type_ID | Type_Description | 
 +---------+------------------+
 |    1    |    Example 1     |
 +---------+------------------+
 |    2    |    Example 2     |
 +---------+------------------+

 +---------+---------------+ 
 | ITEM_ID |   ITEM_TYPE   | 
 +---------+---------------+
 |    1    |       1       |
 +---------+---------------+
 |    2    |       1       |
 +---------+---------------+
 |    3    |       1       |
 +---------+---------------+
 |    4    |       2       |
 +---------+---------------+
 |    5    |       2       |
 +---------+---------------+

 +-----------+---------+------+ 
 | Result_ID | Item_ID | Cost | 
 +-----------+---------+------+ 
 |     1     |    1    |  10  |
 +-----------+---------+------+
 |     2     |    1    |  20  |
 +-----------+---------+------+
 |     3     |    2    |   5  |
 +-----------+---------+------+
 |     4     |    5    |  100 |
 +-----------+---------+------+ 

Desired Output

 +---------+------------+--------------+------+
 | Type_ID | Item_Count | Result_Count | Cost |
 +---------+------------+--------------+------+
 |    1    |      3     |       3      |  35  |
 +---------+------------+--------------+------+
 |    2    |      2     |       1      |  100 |
 +---------+------------+--------------+------+

Answer 1

I think GMB's answer was quite good, but in case there is a type with no items (something in your requirements), it will not be displayed. So first of all let's create the input data:

select 1 as Type_ID, 'Example 1' as Type_Description into #type
union all
select 2, 'Example 2'
union all
select 3, 'Example 3'

select 1 Result_ID, 1 Item_ID, 10 Cost into #item
union all
select 2, 1, 20 Cost
union all
select 3, 2, 5 Cost
union all
select 4, 5, 100 Cost

select 1 Item_ID, 1 Item_Type INTO #item_type
union all
select 2, 1
union all
select 3, 1
union all
select 4, 2
union all
select 5, 2

Note I added also a type 3 with no items to test the no item case.

And then the query you need:

SELECT
    t.Type_ID, 
    COUNT(DISTINCT it.Item_ID) Item_Count,
    COUNT(DISTINCT i.Result_ID) Result_Count, 
    SUM(ISNULL(Cost, 0)) Cost
FROM #type t
LEFT JOIN #item_type it on it.Item_Type = t.Type_ID
LEFT JOIN #item i on i.Item_ID = it.Item_ID
GROUP BY t.Type_ID

I think it is pretty straightforward and doesn't need much explanation, but feel free to ask in the comments if necessary.

The results are just like you requested, with also a line for type 3:

 +---------+------------+--------------+------+
 | Type_ID | Item_Count | Result_Count | Cost |
 +---------+------------+--------------+------+
 |    1    |      3     |       3      |  35  |
 +---------+------------+--------------+------+
 |    2    |      2     |       1      |  100 |
 +---------+------------+--------------+------+
 |    3    |      0     |       0      |  0   |
 +---------+------------+--------------+------+

You mentioned that the count of items could be 0 and also the count of results. But aren't both values always either 0, or both > 0? Only in case your type-item many-to-many table doesn't have a FK, you could have that scenario. For example, if I add:

insert into #item_type
select 6, 3

Then the last row is:

 +---------+------------+--------------+------+
 |    3    |      1     |       0      |  0   |
 +---------+------------+--------------+------+

I am not sure if that makes sense in your scenario, but as your post implies that items and results can be 0 independently, that confused me a bit.

Answer 2

You can join and aggregate. Assuming that your tables are called types, types_costs and costs, that would be:

select 
    t.type_id, 
    count(distinct tc.item_id) item_count, 
    count(distinct c.result_id) result_count, 
    sum(c.cost) cost
from types t
inner join types_costs tc on tc.item_type = t.type_id
left costs c on c.item_id = tc.item_id
group by t.type_id

An important thing is to use a left join to bring the costs table so item_ids that do not exist in costs are not eliminated before you get a change to count them. Depending on your actual use case, you might also want a left join on table types_costs.