Join columns and sum the value in one of the columns

Transact-SQL
1

hi,
I have the table Passagerare (with the columns Flightnummer, Avgdatum Biljettyp and the table Biljetttyp with the columns Biljettyp and Pris

I want to show a specific Flightnumber (for example SK405) the Avgdatum for that flight and a new Column Intäkter that will show the sum of the column Pris were the Flightnumber is SK405

I tried something like this but can´t get it right...

SELECT Passagerare.Flightnummer, Passagerare.Avgdatum, Passagerare.Biljettyp, Biljetttyp.Pris
FROM Passagerare 

inner Join Biljetttyp
ON Passagerare.Biljettyp = Biljetttyp.Biljettyp
WHERE Passagerare.Flightnummer = 'SK405'

SELECT SUM(Pris) AS Intäkter FROM Biljetttyp

Then in the next step I want to create a view with all the flights and their intäkter (the sum of the column Pris) for each flight...

2

@sanna,
If you post DDL statements to create your tables and insert statements to populate them with a small amount of data, Then show us what result you are looking for. This will give enough information for someone to post a solution.

3

I have created the tables with:

CREATE TABLE Biljetttyp
(
Biljettnr Integer NOT NULL IDENTITY,
Flightnummer VarChar(50),
Biljettyp VarChar(50),
Pris Integer,
primary key (Biljettnr)
);


CREATE TABLE Passagerare
(
Nr Integer NOT NULL IDENTITY, 
Namn VarChar(255),
Typ VarChar(30),
Passning Bit,
Platsnr Integer,
Biljettyp VarChar(255),
Flightnummer VarChar(50),
Avgdatum Date
CONSTRAINT PassagerarePK
primary key (Nr),
CONSTRAINT PassagerareFlightFK
foreign key (Flightnummer,Avgdatum) references Flight(Flightnummer,Avgdatum)
ON DELETE CASCADE ON UPDATE
4 
INSERT INTO Passagerare (Namn,Typ,Passning,Platsnr,Biljettyp,Flightnummer,Avgdatum)
VALUES
('Marty Feldman','Man',0,NULL,'Återbetalningsbar','LF550','2013-08-01'),
('Jens Christiansen','Man',1,NULL,'Ombokningsbar','LF550','2013-08-01'),
('Martii Turunen','Barn',0,NULL,'Ombokningsbar','SK405','2013-08-01'),
('Heino Turunen','Man',0,NULL,'Ombokningsbar','SK405','2013-08-01'),
('Regine FairChild','Kvinna',1,NULL,'Återbetalningsbar','VR111','2013-08-01'),
('Tina Gottlieb','Barn',1,NULL,'Ombokningsbar','BA801','2013-08-01'),
('Måns Uman','Man',0,NULL,'Återbetalningsbar','LK202','2013-08-01'),
('Collette Ronnan','Kvinna',0,NULL,'Återbetalningsbar','AF330','2013-08-01'),
('Mary Ottosson','Kvinna',0,NULL,'Röd','LK202','2013-08-01'),
('Panu Onninen','Barn',1,NULL,'Återbetalningsbar','SK405','2013-08-01'),
('Gary Eastman','Man',0,NULL,'Återbetalningsbar','LF550','2013-08-01'),
('Lorry Arguent','Kvinna',1,NULL,'Röd','','2013-08-01'),
('Jesper Tuborg','Man',1,NULL,'Ombokningsbar','BA801','2013-08-01'),
('Toyota Corolla','Kvinna',0,NULL,'Återbetalningsbar','LF550','2013-08-02');

INSERT INTO Biljetttyp (Flightnummer,Biljettyp,Pris)
VALUES
('LK202','Återbetalningsbar',1000),
('LK202','Ombokningsbar',500),
('LK202','Röd',400),
('SK405','Återbetalningsbar',1800),
('SK405','Ombokningsbar',900),
('SK405','Röd',750),
('VR111','Återbetalningsbar',4800),
('VR111','Ombokningsbar',2400),
('VR111','Röd',2000),
('LF550','Återbetalningsbar',2200),
('LF550','Ombokningsbar',1100),
('LF550','Röd',800),
('BA801','Återbetalningsbar',1900),
('BA801','Ombokningsbar',950),
('BA801','Röd',700),
('AF330','Återbetalningsbar',1600),
('AF330','Ombokningsbar',800),
('AF330','Röd',600);
5

Will this work:

SELECT
    Passagerare.Flightnummer
  , Passagerare.Avgdatum
  , Passagerare.Biljettyp
  , Biljetttyp.Pris
  , Sum(Pris) OVER (PARTITION BY Passagerare.Flightnummer)  AS Intäkter
FROM
    Passagerare
INNER JOIN Biljetttyp ON Passagerare.Biljettyp = Biljetttyp.Biljettyp
WHERE
    Passagerare.Flightnummer = 'SK405';

6

Unfortunately not

It is only 3 people booked at flight SK405 and I want the column intäkter to show the sum of those values from the column Pris from the table biljetttyp so the output should be something like

Flightnummer   Avgdatum           Intäkter
SK405              2013-08-01     3600
7 
SELECT
   p.Flightnummer
 , Avgdatum
 , Sum(Pris) SumPris
FROM
    Passagerare p
INNER JOIN Biljetttyp b
ON p.Biljettyp = b.Biljettyp
WHERE
   p.Flightnummer = 'SK405'
GROUP BY
   p.Flightnummer
 , Avgdatum    ;

1 Like
8

Probably need to join on flightnummer as well:

select p.flightnummer
      ,p.avgdatum
      ,sum(b.pris) as [intäkter]
  from passagerare as p
       inner join biljetttyp as b
               on b.flightnummer=p.flightnummer
              and b.biljettyp=p.biljettyp
 where p.flightnummer='SK405'
   and p.avgdatum='2013-08-01'
 group by p.flightnummer
         ,p.avgdatum
;
1 Like
9

Yes, that´s it. Thanks a lot

10

CONSTRAINT PassagerareFlightFK
foreign key (Flightnummer,Avgdatum) references Flight(Flightnummer,Avgdatum)

i am confused regading this constraint. where is the table Flight