SQL - Total Row Values Meeting Criteria-CodePudding

I have a table and I want to sum rows that meet a certain criteria. Table looks like this:

| Product | Sales_Num | Week | Cost | Retail | 
|:------- |:---------:|:----:|:----:| ------:|
| PLA     | 45281     | 38   | 53   | 88     |  
| PLA2    | 45281     | 38   | 3    | 4      |
| CR25    | 45281     | 38   | 99   | 250    |
| BA34    | 45281     | 38   | 74   | 99     |
| PLA     | 40251     | 38   | 53   | 75     |
| PLA2    | 40251     | 38   | 2    | 5      |
| CR25    | 40251     | 38   | 99   | 200    |
| BA34    | 40251     | 38   | 74   | 88     |

I want to Calculate the RETAIL column WHERE Product IN ('PLA','PLA2') AND Week = 38 and Sales_Num = 45281

Essentially, I want to Add 88 4 (first 2 rows above meet criteria). I want to eventually turn this into a function where I pass in Product, Week, and Sales_Num and I write the Calculation, Sales_Num, and Week to another table.

I was able to sum the rows I want, but I want the output to be [Sales_Num],[Week],[Total_Retail]

SELECT (
        (SUM(CASE WHEN [Product]='PLA' AND [Sales_Num]=45281 AND [WEEK]=38 THEN [Retail] END)  
         SUM(CASE WHEN [Product]='PLA2' AND [Sales_Num]=45281 AND [WEEK]=38 THEN [Retail] END)
         )
        ) AS Total_Retail

CodePudding user response：

select   Sales_Num
        ,week
        ,sum(Retail) as Total_Retail
from     t
where    Week = 38 
and      Sales_Num = 45281
and      Product in('PLA', 'PLA2')
group by Sales_Num, week

Sales_Num	week	Total_Retail
45281	38	92

Fiddle

CodePudding user response：

SELECT Sales_Num, [Week], SUM(Retail) AS Total_Retail
FROM @yourtable
WHERE [Week] = 38
AND Sales_Num = 45281
AND Product IN ('PLA', 'PLA2')
GROUP BY Sales_Num, [Week]