CODEWITHSUNDEEP
sqlsql-servert-sql
Bungicasse
Bungicasse

Reputation: 145

How do I get sum of identical rows in a join

I got a problem with my SQL code, I use multiple joins from different tables and my code looks like this:

SELECT Person.navn, Vare.varenavn, Ordre.antal FROM Ordre
JOIN Person
ON Person.Id = Ordre.P_id
JOIN Vare
ON Vare.Id = Ordre.vareid

My problem is that it turns out like this

enter image description here

I want it to remove duplicates and add the numbers together in the "antal" column so it should be: "Tulle - Banan - 9"

Thank you!

Edit:

I changed my datatypes from text to nvchar and varchar. I used @lad2025's top answer.

Upvotes: 1

Views: 45

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175796

Looks like you need grouping and SUM aggregate function:

SELECT Person.navn, Vare.varenavn, SUM(Ordre.antal) AS antal
FROM Ordre
JOIN Person
  ON Person.Id = Ordre.P_id
JOIN Vare
  ON Vare.Id = Ordre.vareid
GROUP BY Person.navn, Vare.varenavn;

EDIT:

Do not use obsolete types text/ntext/image, for now you can use CAST but you should consider changing column datatype:

SELECT CAST(Person.navn AS NVARCHAR(MAX)),
       CAST(Vare.varenavn AS NVARCHAR(MAX)),
       SUM(Ordre.antal) AS antal
FROM Ordre
JOIN Person
  ON Person.Id = Ordre.P_id
JOIN Vare
  ON Vare.Id = Ordre.vareid
GROUP BY CAST(Person.navn AS NVARCHAR(MAX)), CAST(Vare.varenavn AS NVARCHAR(MAX));

ntext, text, and image:

ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

Upvotes: 2

Related Questions