This is a query that produces a table with garbage data, but (I think) will get the point across of what I need. SQL Server 2008

Create Table SanitationGarbage
(
	saleid int
	,projectname varchar(200)
	,typeofsale varchar(200)
)

Insert Into SanitationGarbage VALUES
(1, 'May Rich', 'Final'), (2, 'May Rich', 'Final'), (3, 'May Rich', 'Pending')
,(4, 'April Rich', 'Final'), (5, 'April Rich', 'Pending'), (6, 'April Rich', 'Pending')
,(7, 'May Frank', 'Pending'), (8, 'May Frank', 'Final'), (9, 'May Frank', 'Pending')
,(10, 'April Frank', 'Final'), (11, 'April Frank', 'Final'), (12, 'May Arm', 'Final')
,(13, 'May Arm', 'Pending')

Now my select query below does not group the data like I need it to, nor does it show a total row like I need it to.  How does this need to be written so the data is displayed in the proper formatting?

Select 
projectname
,Count(saleID) As [Total Sales]
,Count(case when typeofsale = 'Final' then saleID else null end) As [Final Sales]
,Count(case when typeofsale = 'Pending' then saleID else null end) As [Pending Sales]
FROM SanitationGarbage
GROUP BY projectname 
order by projectname asc

Which provides the below output

projectname	Total Sales	Final Sales	Pending Sales
April Frank	2	2	0
April Rich	3	1	2
May Arm	2	1	1
May Frank	3	1	2
May Rich	3	2	1

And this is my desired output

projectname	Total Sales	Final Sales	Pending Sales
May Arm	2	1	1
Arm Totals	2	1	1
April Frank	2	2	0
May Frank	3	1	2
Frank Totals	5	3	2
April Rich	3	1	2
May Rich	3	2	1
Rich Totals	6	3	3

your projectname field is actually two separate fields that you need to do aggrigations on, month and salesman,

you either need to break up the field programmatically or ideally adjust your table to carry monthid and salesmanid instead of varchar datatypes, SQL Server works better with numbers :)

Once you do that you can use GROUPING SET  or window function such as OVER (PARTITION BY) functionality to accomplish your desired result set.

• Edited by SQLGru 11 hours 59 minutes ago

You need one more element (column) in the grouping columns and be able to rollup. You can use some parsing or add the right column.

DECLARE @T table
(
	saleid int NOT NULL,
	sales_rep varchar(15) NOT NULL,
	projectname varchar(200),
	typeofsale varchar(200)
);

INSERT INTO @T VALUES
    (1, 'Rich', 'May Rich', 'Final'), (2, 'Rich', 'May Rich', 'Final'), (3, 'Rich', 'May Rich', 'Pending'),
    (4, 'Rich', 'April Rich', 'Final'), (5, 'Rich', 'April Rich', 'Pending'), (6, 'Rich', 'April Rich', 'Pending'),
    (7, 'Frank', 'May Frank', 'Pending'), (8, 'Frank', 'May Frank', 'Final'), (9, 'Frank', 'May Frank', 'Pending'),
    (10, 'Frank', 'April Frank', 'Final'), (11, 'Frank', 'April Frank', 'Final'), 
    (12, 'Arm', 'May Arm', 'Final'),(13, 'Arm',  'May Arm', 'Pending');

SELECT 
    sales_rep,
    CASE WHEN GROUPING(projectname) = 0 THEN projectname ELSE 'Totals' END projectname,
    COUNT(*) AS [Total Sales],
    SUM(CASE WHEN typeofsale = 'Final' THEN 1 ELSE 0 END) AS [Final Sales],
    SUM(CASE WHEN typeofsale = 'Pending' THEN 1 ELSE 0 END) AS [Pending Sales]
FROM 
    @T
GROUP BY
    GROUPING SETS (
    (sales_rep, projectname),
    (sales_rep)
    )
ORDER BY
    sales_rep,
    GROUPING(projectname),
    projectname ASC;
GO

I will leave the parsing one for you as an exercise ;)

Try

;with cte as (select ProjectName, substring(projectName, nullif(charindex(' ', projectName),0) + 1, len(ProjectName)) as LName, saleId,
typeOfSale from #SanitationGarbage)

select case when grouping(lName) = 0 then lName + ' Totals'  else ProjectName end as ProjName
,count(SaleId) as [Total Sales]
,Count(case when typeofsale = 'Final' then saleID else null end) As [Final Sales]
,Count(case when typeofsale = 'Pending' then saleID else null end) As [Pending Sales]

from cte 
group by grouping sets(projectname, lName)
order by case when GROUPING_Id(lName) = 0 then lName else substring(projectName, nullif(charindex(' ', projectName),0) + 1, len(ProjectName)) end,
GROUPING_Id(projectname)

Create Table SanitationGarbage
(
	saleid int
	,projectname varchar(200)
	,typeofsale varchar(200)
)

Insert Into SanitationGarbage VALUES
(1, 'May Rich', 'Final'), (2, 'May Rich', 'Final'), (3, 'May Rich', 'Pending')
,(4, 'April Rich', 'Final'), (5, 'April Rich', 'Pending'), (6, 'April Rich', 'Pending')
,(7, 'May Frank', 'Pending'), (8, 'May Frank', 'Final'), (9, 'May Frank', 'Pending')
,(10, 'April Frank', 'Final'), (11, 'April Frank', 'Final'), (12, 'May Arm', 'Final')
,(13, 'May Arm', 'Pending')


Select Name, ISNULL(mnth,'Totals') mnth, [Total Sales], [Final Sales],  [Pending Sales] from (
Select 
stuff(projectname, 1,charindex(' ',projectname),'') Name, Left(projectname, charindex(' ',projectname)) mnth
,Count(saleID) As [Total Sales]
,Count(case when typeofsale = 'Final' then saleID else null end) As [Final Sales]
,Count(case when typeofsale = 'Pending' then saleID else null end) As [Pending Sales]
FROM SanitationGarbage
 Where stuff(projectname, 1,charindex(' ',projectname),'') is not null
GROUP BY stuff(projectname, 1,charindex(' ',projectname),''), Left(projectname, charindex(' ',projectname))
With Rollup
) t
WHERE Name is not null

order by Name,Nullif(mnth,'Totals')  desc  

drop table SanitationGarbage