SQL Server 2016

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

--Master
CREATE TABLE [Master]
(
SPID  int NOT NULL
             IDENTITY(1, 1),
	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]
(
SPID int,
	ProjectID		INT,
	[Length]		FLOAT,
	TenthReading	INT
)

INSERT INTO [Details]
VALUES (1,123, 0.1, 43),
	   (1,123, 0.1, 45),
	   (1,123, 0.1, 46),
	   (1,123, 0.1, 55),
	   (1,123, 0.1, 59), 
	   (1,123, 0.060, 120),
	   (2,145, 0.1, 130),
	   (2,145, 0.1, 45),
	   (2,145, 0.1, 46),
	   (2,145, 0.1, 55),
	   (2,145, 0.1, 59), 
	   (2,145, 0.080, 140),
	   (3,423, 0.077, 43),
	   (3,423, 0.1, 45),
	   (3,423, 0.1, 46),
	   (3,423, 0.1, 155),
	   (3,423, 0.1, 59), 
	   (3,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]

SPID	ProjectID	DesignMix	ProjectType	Date	ContentType
1	123	AB-12	New Proj	01/01/2015 00:00:00	CT_1
2	145	AR-13	New Proj	01/02/2015 00:00:00	CT_2
3	423	AB-13	New Proj	01/01/2015 00:00:00	CT_3

SPID	ProjectID	Length	TenthReading
1	123	0.1	43
1	123	0.1	45
1	123	0.1	46
1	123	0.1	55
1	123	0.1	59
1	123	0.06	120
2	145	0.1	130
2	145	0.1	45
2	145	0.1	46
2	145	0.1	55
2	145	0.1	59
2	145	0.08	140
3	423	0.077	43
3	423	0.1	45
3	423	0.1	46
3	423	0.1	155
3	423	0.1	59
3	423	0.08	99

ProjectID	DesignMix	Material	Perc	ContentType
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
Distinct
z.ContentType

,z.Material
,sum(CountLength) over (partition by Material,ContentType order by ContentType) CountLength
,sum(SumLength) over (partition by Material,ContentType order by ContentType) SumLength
,sum(AvgR) over (partition by Material,ContentType order by ContentType) AvgR
,sum(MinR) over (partition by Material,ContentType order by ContentType)MinR
,sum(MaxR) over (partition by Material,ContentType order by ContentType)MaxR

,CountRgreater95
,SumLengthgreater95 
From
(
Select 
x.ContentType
,CountLength
,SumLength
, AvgR
,MinR
,MaxR
,x.ProjectID
,x.DesignMix
,Coalesce(y.Material,x.Material) Material
,CountRgreater95
,SumLengthgreater95
From 
(Select Distinct
c.ContentType
,CountLength
,SumLength
, AvgR
,MinR
,MaxR
,c.ProjectID
,c.DesignMix
,c.Material

,CountRgreater95
,SumLengthgreater95

from
(
select distinct a.* ,b.Material,max(b.Perc) over (partition by b.Material,b.DesignMix order by a.ProjectID) Perc from  (SELECT a.ProjectID,a.DesignMix,a.ContentType, COUNT(b.Length) AS CountLength, SUM(b.Length) AS SumLength, CONVERT(int, ROUND(AVG(CONVERT(decimal(6, 2), b.TenthReading)), 0)) AS AvgR, MIN(b.TenthReading) AS MinR, MAX(b.TenthReading) AS MaxR, c.CountRgreater95, 
        c.SumLengthgreater95
FROM            dbo.Master AS a INNER JOIN
dbo.Details AS b ON a.SPID = b.SPID INNER JOIN
(SELECT        x0.ContentType, COUNT(x.Length) AS CountRgreater95, SUM(x.Length) AS SumLengthgreater95
FROM            dbo.Master AS x0 INNER JOIN
dbo.Details AS x ON x0.SPID = x.SPID
WHERE        (x.TenthReading >= 95) AND (x0.ProjectType = 'New Proj') AND (YEAR(x0.Date) = 2015)
GROUP BY x0.ContentType) AS c ON a.ContentType = c.ContentType
WHERE        (a.ProjectType = 'New Proj') AND (YEAR(a.Date) = 2015) 
GROUP BY a.ContentType, YEAR(a.Date), c.CountRgreater95, c.SumLengthgreater95,a.ProjectID,a.DesignMix  )a inner join MaterialType b on a.ProjectID=b.ProjectID and a.DesignMix=b.DesignMix

) c  group by c.ContentType,ProjectID,DesignMix,Material,CountLength,SumLength,CountRgreater95,SumLengthgreater95,AvgR,MinR,MaxR
)x
Left Join
(
select d.ContentType
,d.ProjectID
,d.DesignMix
,'Mixed' as Material
 ,count(ProjectID) cnt
 from
 (
Select  Distinct
c.ContentType
,CountLength
,SumLength
, AvgR
,MinR
,MaxR
,c.ProjectID
,c.DesignMix
,c.Material
,CountRgreater95
,SumLengthgreater95

from
(
select distinct a.* ,b.Material,max(b.Perc) over (partition by b.Material,b.DesignMix order by a.ProjectID) Perc from  (SELECT a.ProjectID,a.DesignMix,a.ContentType, COUNT(b.Length) AS CountLength, SUM(b.Length) AS SumLength, CONVERT(int, ROUND(AVG(CONVERT(decimal(6, 2), b.TenthReading)), 0)) AS AvgR, MIN(b.TenthReading) AS MinR, MAX(b.TenthReading) AS MaxR, c.CountRgreater95, 
        c.SumLengthgreater95
FROM            dbo.Master AS a INNER JOIN
dbo.Details AS b ON a.SPID = b.SPID INNER JOIN
(SELECT        x0.ContentType,x1.Material, COUNT(x.Length) AS CountRgreater95, SUM(x.Length) AS SumLengthgreater95
FROM            dbo.Master AS x0 INNER JOIN
dbo.Details AS x ON x0.SPID = x.SPID left Join
MaterialType x1 on x0.ProjectID=x1.ProjectID and x0.DesignMix=x1.DesignMix
WHERE        (x.TenthReading >= 95) AND (x0.ProjectType = 'New Proj') AND (YEAR(x0.Date) = 2015)
GROUP BY x0.ContentType,x1.Material) AS c ON a.ContentType = c.ContentType
WHERE        (a.ProjectType = 'New Proj') AND (YEAR(a.Date) = 2015) 
GROUP BY a.ContentType, YEAR(a.Date), c.CountRgreater95, c.SumLengthgreater95,a.ProjectID,a.DesignMix  )a inner join MaterialType b on a.ProjectID=b.ProjectID and a.DesignMix=b.DesignMix

) c  group by c.ContentType,ProjectID,DesignMix,Material,CountLength,SumLength,CountRgreater95,SumLengthgreater95,AvgR,MinR,MaxR
)d
group by d.ContentType,ProjectID,DesignMix 
Having count(ProjectID)>1
)y on x.ContentType=y.ContentType and x.ProjectID=y.ProjectID and x.DesignMix=y.DesignMix
)z

ContentType	Material	CountLength	SumLength	AvgR	MinR	MaxR	CountRgreater95	SumLengthgreater95
CT_1	Mixed	12	1.12	122	86	240	1	0.06
CT_2	Concrete	6	0.58	79	45	140	2	0.18
CT_3	Limestone	6	0.557	75	43	155	2	0.18