SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

--Master
CREATE TABLE [Master]
(
	ProjectID	INT,
	DesignMix	VARCHAR(100),
	ProjectType	VARCHAR(100),
	[Date]		DATE,
	ContentType	VARCHAR(100)
)

INSERT INTO [Master]
VALUES (123, 'AB-12', 'New Proj', '1/1/2015', 'CT_1'),  
       (145, 'AR-13', 'New Proj', '2/1/2015', 'CT_2'),  
       (423, 'AB-13', 'New Proj', '1/1/2015', 'CT_3')

-- Details
CREATE TABLE [Details]
(
	ProjectID	INT,
	[Length]	FLOAT,
	TenthReading	INT
)

INSERT INTO [Details]
VALUES (123, 0.1,    43),
       (123, 0.1,    45),
       (123, 0.1,    46),
       (123, 0.1,    55),
       (123, 0.1,    59), 
       (123, 0.060, 120),
       (145, 0.1,   130),
       (145, 0.1,    45),
       (145, 0.1,    46),
       (145, 0.1,    55),
       (145, 0.1,    59), 
       (145, 0.080, 140),
       (423, 0.077,  43),
       (423, 0.1,    45),
       (423, 0.1,    46),
       (423, 0.1,   155),
       (423, 0.1,    59), 
       (423, 0.080,  99)   

--MaterialType
CREATE TABLE [MaterialType]
(
	ProjectID	INT,
	DesignMix	VARCHAR(100),
	Material	VARCHAR(100),
	Perc		INT,
	ContentType	VARCHAR(100)
)

INSERT INTO [MaterialType]
VALUES (123, 'AB-12', 'Concrete ', 20, 'CT_1'), 
       (123, 'AB-12', 'Limestone', 60, 'CT_1'), 
       (123, 'AB-15', 'Concrete ', 20, 'CT_1'), 
       (145, 'AR-13', 'Concrete ', 20, 'CT_2'),
       (145, 'AR-13', 'Concrete ', 70, 'CT_2'),
       (423, 'AB-13', 'Limestone', 80, 'CT_3')

/*
SELECT *
FROM [Master]

SELECT *
FROM [Details]

SELECT *
FROM [MaterialType]
*/
27 rows affected

select ProjectID,
       DesignMix,
       case when count(distinct Material) > 1 then 'Mixed' else min(Material) end as 'Material',
       ContentType

from   MaterialType

group by ProjectID, DesignMix, ContentType

order by 1,2,4


ProjectID
DesignMix
Material
ContentType


123
AB-12
Mixed
CT_1

123
AB-15
Concrete
CT_1

145
AR-13
Concrete
CT_2

423
AB-13
Limestone
CT_3


select  ProjectID,
        count(Length)                                           as CountLength,
        convert(numeric(6,2),sum(Length))                       as SumLength,
        convert(numeric(6,2),avg(TenthReading*1.0))             as AvgR,
        min(TenthReading)                                       as MinR,
        max(TenthReading)                                       as MaxR,
        sum(case when TenthReading > 95 then 1      else 0 end) as CountRgreater95,
        sum(case when TenthReading > 95 then Length else 0 end) as SumLengthgreater95

from    Details

group by ProjectID
order by ProjectID


ProjectID
CountLength
SumLength
AvgR
MinR
MaxR
CountRgreater95
SumLengthgreater95


123
6
0.56
61.33
43
120
1
0.06

145
6
0.58
79.17
45
140
2
0.18

423
6
0.56
74.50
43
155
2
0.18


with

mat_type as
(select ProjectID,
        DesignMix,
        case when count(distinct Material) > 1 then 'Mixed' else min(Material) end as 'Material',
        ContentType

from    MaterialType

group by ProjectID, DesignMix, ContentType),

dtls as
(select ProjectID,
        count(Length)                                           as CountLength,
        convert(numeric(6,3),sum(Length))                       as SumLength,
        convert(numeric(6,2),avg(TenthReading*1.0))             as AvgR,
        min(TenthReading)                                       as MinR,
        max(TenthReading)                                       as MaxR,
        sum(case when TenthReading > 95 then 1      else 0 end) as CountRgreater95,
        sum(case when TenthReading > 95 then Length else 0 end) as SumLengthgreater95

from    Details

group by ProjectID)

select  m.ContentType,
        mt.Material,
        d.CountLength,
        d.SumLength,
        d.AvgR,
        d.MinR,
        d.MaxR,
        d.CountRgreater95,
        d.SumLengthgreater95
        
from    Master m

left
join    dtls d
on      d.ProjectID = m.ProjectID

left
join    mat_type mt
on      mt.ProjectID   = m.ProjectID
and     mt.DesignMix   = m.DesignMix
and     mt.ContentType = m.ContentType

where   m.ProjectTYpe = 'New Proj'
and     year(m.[Date]) = 2015

order by m.ContentType


ContentType
Material
CountLength
SumLength
AvgR
MinR
MaxR
CountRgreater95
SumLengthgreater95


CT_1
Mixed
6
0.560
61.33
43
120
1
0.06

CT_2
Concrete
6
0.580
79.17
45
140
2
0.18

CT_3
Limestone
6
0.557
74.50
43
155
2
0.18

ProjectID	DesignMix	Material	ContentType
123	AB-12	Mixed	CT_1
123	AB-15	Concrete	CT_1
145	AR-13	Concrete	CT_2
423	AB-13	Limestone	CT_3

ProjectID	CountLength	SumLength	AvgR	MinR	MaxR	CountRgreater95	SumLengthgreater95
123	6	0.56	61.33	43	120	1	0.06
145	6	0.58	79.17	45	140	2	0.18
423	6	0.56	74.50	43	155	2	0.18

ContentType	Material	CountLength	SumLength	AvgR	MinR	MaxR	CountRgreater95	SumLengthgreater95
CT_1	Mixed	6	0.560	61.33	43	120	1	0.06
CT_2	Concrete	6	0.580	79.17	45	140	2	0.18
CT_3	Limestone	6	0.557	74.50	43	155	2	0.18