Sums of the values of a table, but according to the name of another field of this same table.

Question

Hello,

I would like to select the sums of the values of a table, but according to the name of another field of this same table.

Here is a screenshot of the table in question, with the values I would like to sum.
Each red frame should be named like the yellow highlighted row, and the field "masseProduit" should be summed.

Thanks in advance for your answers,

Sincerely,

Joshua Kaczmarek.

Answer 1

As Olaf noted, giving us the DDL and DML and desired result helps us help you. Without that, we are oftentimes guessing what you want, but if you give us that, we can often provide a tested solution. So the following is a guess at what you want as well as an example of how you might provide the DDL and DML and desired result.

Create Table YourTableName(ligne varchar(20), masseProduit int);
Insert YourTableName Values
('CT150', 25),
('CT250', 75),
('CT-TANDEM', 300),
('ST1', 125),
('ST2', 275),
('ST-TANDEM', 3300),
('RT25', 999);

;With cte As
(Select Left(t.ligne, 2) + '-TANDEM' As ligne, t.masseProduit
From YourTableName t
Where Exists (Select * From YourTableName t1 Where Left(t.ligne, 2) + '-TANDEM' = t1.ligne))
Select ligne, Sum(masseProduit) As masseProuditSum
From cte
Group By ligne;

/* Desired Result
ligne      masseProuditSum
CT-TANDEM  400
ST-TANDEM  3700
*/

Tom

Answer 2

Hi @Joshua Kaczmarek ,

Welcome to the microsoft TSQL Q&A forum!

The following is my guessed solution, please check:

 Create Table #YourTableName(ligne varchar(20), masseProduit int);  
 Insert INTO #YourTableName Values('CT1250', 1295),('CT1600', 7763),  
                                  ('CT-TANDEM',1259),('ST1',2244),  
                                  ('ST2',6247),('ST-TANDEM',1904),  
                                  ('LYRE',1625),('TC600',6988),  
   ('CD2',5721),('CDK1',1052)  
SELECT * FROM #YourTableName  
  
;WITH cte  
as(SELECT LEFT(ligne,2)+'-TANDEM' AS ligne,masseProduit  
FROM #YourTableName  
WHERE LEFT(ligne,2)   
IN(SELECT LEFT(ligne,2)  
  FROM #YourTableName  
  WHERE ligne LIKE '%-TANDEM'))  
  
SELECT DISTINCT ligne,SUM(masseProduit) OVER(PARTITION BY ligne) ssum  
FROM cte  

Output:

Or:

;WITH cte  
as(SELECT LEFT(ligne,2)+'-TANDEM' AS ligne,masseProduit  
FROM #YourTableName  
WHERE LEFT(ligne,2)   
IN(SELECT LEFT(ligne,2)  
   FROM #YourTableName  
   WHERE ligne LIKE '%-TANDEM'))  
  
SELECT DISTINCT ligne,SUM(masseProduit) OVER(PARTITION BY ligne) ssum  
FROM cte  
UNION ALL  
SELECT DISTINCT ligne,SUM(masseProduit) OVER(PARTITION BY ligne) ssum  
FROM #YourTableName  
WHERE LEFT(ligne,2) NOT IN   
 (SELECT LEFT(ligne,2)  
  FROM #YourTableName  
  WHERE ligne LIKE '%-TANDEM')  

Output:

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hello !

Thank you all for your answers, it worked.

I accepted the answer and I can now close the question.

Best Regards,

Joshua Kaczmarek.