CASE condition and SUM() common table expressions or Group By

Hope some can help ? I have a 3rd party software were i can do custom sql querys but not able to change any of the database. Struggling to get the result i would like.

Do I use GROUP BY or CTE to get the result i need?

WITH CTE AS ( 
SELECT  tblTicketsSummary.fldDateScheduled AS [Scheduled], 
tblTicketsRow.fldStartTime  AS [TIME], tblTicketsRow.fldPs AS 
[s],tblTicketsRow.fldPrice AS PR, 
tblTicketsSummary.fldFirstName+' '+tblTicketsSummary.fldLastName 
AS [Client], ROW_NUMBER() OVER 
(PARTITION BY tblTicketsSummary.fldTicketID ORDER BY 
tblTicketsRow.fldTicketID) AS C, tblTicketsSummary.fldDateClosed 
AS [Date Closed],
 CASE WHEN tblTicketsRow.fldPs ='p' THEN tblTicketsRow.fldPrice  
ELSE £0.00 END AS Product,  
CASE WHEN tblTicketsRow.fldPs ='s' THEN tblTicketsRow.fldPrice  
ELSE £0.00 END AS Services, 
FROM tblTicketsSummary INNER JOIN 
tblTicketsRow ON tblTicketsSummary.fldTicketID = tblTicketsRow.fldTicketID
 WHERE (fldDateVoided IS NULL) AND (NOT(fldDateClosed IS NULL)) 
GetUserDate('AND','fldDateClosed','') AND tblTicketsRow.fldEmployeeName ='Tina Young' 
 AND tblTicketsSummary.fldTotal >1 )
SELECT * FROM  CTE   ORDER BY   Scheduled ASC,  TIME ASC

This give me the result below . I have removed Where c=1 in the last select which i will put back in the final query which give me the first result by time which is what i want. The bit i am struggling with is. I would like to add the prices of all products together and the prices of all service together to give a total of products and services sold. Then a grand total of services and product together in the final query .

Scheduled   TIME s   PR      Client   C Date Closed Product  Services  Total
17/07/2020 17:00 S £10.00 Ben Preston 1 17/07/2020  £0.00    £10.00     s+p
17/07/2020 17:45 S £1.00  Ben Preston 2 17/07/2020  £0.00    £1.00      s+p
17/07/2020       P £19.00 Ben Preston 3 17/07/2020  £19.00   £0.00      s+p
17/07/2020       P £10.00 Ben Preston 4 17/07/2020  £10.00   £0.00      s+p

The Result i would like is below. The final part of the query is as below. I am only able to custom query’s in the 3rd party software.

 SELECT * FROM  CTE  WHERE c=1 ORDER BY   Scheduled ASC,  TIME ASC 


Scheduled  TIME   Client       C   Date Closed  Product Services   Total
17/07/2020 17:00 Ben Preston   1   17/07/2020   £29.00   £11.00    £40.00

I hope I have formatted this right for people to understand. Thank you for any help you can provide.

Answer

Seems like you simply need to add two Group Sums to your existing CTE and then add them in your final Select:

WITH CTE AS ( 
SELECT  tblTicketsSummary.fldDateScheduled AS [Scheduled], 
   tblTicketsRow.fldStartTime  AS [TIME], 
   tblTicketsRow.fldPs AS [s],tblTicketsRow.fldPrice AS PR, 
   tblTicketsSummary.fldFirstName+' '+tblTicketsSummary.fldLastName AS [Client], 
   ROW_NUMBER() 
   OVER (PARTITION BY tblTicketsSummary.fldTicketID
         ORDER BY tblTicketsRow.fldStartTime) AS C, 
   tblTicketsSummary.fldDateClosed AS [Date Closed],
   
   SUM(CASE WHEN tblTicketsRow.fldPs ='p' THEN tblTicketsRow.fldPrice ELSE £0.00 END)
   OVER (PARTITION BY tblTicketsSummary.fldTicketID) AS Product,  
   
   SUM(CASE WHEN tblTicketsRow.fldPs ='s' THEN tblTicketsRow.fldPrice ELSE £0.00 END)
   OVER (PARTITION BY tblTicketsSummary.fldTicketID) AS Services
FROM tblTicketsSummary
INNER JOIN tblTicketsRow
ON tblTicketsSummary.fldTicketID = tblTicketsRow.fldTicketID
WHERE (fldDateVoided IS NULL) AND (NOT(fldDateClosed IS NULL)) 
--GetUserDate('AND','fldDateClosed','') AND tblTicketsRow.fldEmployeeName ='Tina Young' 
 AND tblTicketsSummary.fldTotal >1 )
SELECT cte.*,
   Product + Services as total
FROM  CTE
WHERE C = 1
ORDER BY   Scheduled ASC,  TIME ASC;

Btw, the ORDER BY tblTicketsRow.fldTicketID on your ROW_NUMBER is not based on a unique column, thus the order of rows is not guaranteed. You probably want order by tblTicketsRow.fldStartTime instead.