Klassendurchschnitt SQL

Question

Hallo,

mit der unteren Abfrage bekomme ich das angezeigte Ergebnis welches man auf dem Bild sieht. Mein Ziel ist nun den Klassendurschnitt zu errechnen. Kann man sowas auch in SQL machen? Soweit ich weiß gibt es die Funktion AVG() aber geht dies auch in meinen Fall?

VG

---

Kalle

Answer 1

Hallo Kalle,

das hier ist ja eher ein gewichteter Durchschnitt (mit einer kuriosen Formel), direkt mit AVG geht es nicht, aber es lässt sich ja leit ausrechnen. Mal in der Annahme, das Du die Klammern vergessen hast und für das erste Model die Formel mit (3*1 + 4*2)/3 richtig wäre, kannst Du es per SQL so ausrechnen:

SELECT Sub.Hersteller
      ,SUB.Model
      ,SumRang / SumGebote AS Durchschnitt
FROM
   (SELECT Hersteller
          ,Modell
          ,SUM(Rang * AnzahlGebote) AS SumRang
          ,SUM(AnzahlGebote) AS SumGebote
    FROM DWH.etl.Archive_Autos
    GROUP BY Hersteller
            ,Model) AS Sub

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

Hallo Kalle, du könntest das über eine Common Table Expression (CTE) lösen.

Declare @Archive_Autos as Table(PK integer identity, Hersteller varchar(10),
Modell varchar(10), Rang integer);

Insert into @Archive_Autos(Hersteller, Modell, Rang) values('AUDI', 'A3',
2);
Insert into @Archive_Autos(Hersteller, Modell, Rang) values('BMW', 'Series
1', 3);
Insert into @Archive_Autos(Hersteller, Modell, Rang) values('BMW', 'Series
1', 4);
Insert into @Archive_Autos(Hersteller, Modell, Rang) values('BMW', 'Series
1', 4);

Insert into @Archive_Autos(Hersteller, Modell, Rang) values('BMW', 'Series
3', 4);
Insert into @Archive_Autos(Hersteller, Modell, Rang) values('BMW', 'Series
3', 4);
Insert into @Archive_Autos(Hersteller, Modell, Rang) values('BMW', 'Series
3', 4);
Insert into @Archive_Autos(Hersteller, Modell, Rang) values('BMW', 'Series
3', 4);
Insert into @Archive_Autos(Hersteller, Modell, Rang) values('BMW', 'Series
3', 4);

Insert into @Archive_Autos(Hersteller, Modell, Rang) values('CITROEN',
'BERLINGO', 2);
Insert into @Archive_Autos(Hersteller, Modell, Rang) values('CITROEN',
'BERLINGO', 4);

Insert into @Archive_Autos(Hersteller, Modell, Rang) values('CITROEN', 'C1',
2);
Insert into @Archive_Autos(Hersteller, Modell, Rang) values('CITROEN', 'C1',
3);
Insert into @Archive_Autos(Hersteller, Modell, Rang) values('CITROEN', 'C1',
4);
Insert into @Archive_Autos(Hersteller, Modell, Rang) values('CITROEN', 'C1',
4);

Insert into @Archive_Autos(Hersteller, Modell, Rang) values('CITROEN', 'C3',
4);

Insert into @Archive_Autos(Hersteller, Modell, Rang) values('DAEWOO',
'LANOS', 1);

Select Hersteller, Modell, Rang, count(*) as Anzahl_Gebote
from @Archive_Autos
Group by Hersteller, Modell, Rang
order by Hersteller, Modell;

With CTE
as
(Select Hersteller, Modell, Rang, count(*) as Anzahl_Gebote
from @Archive_Autos
Group by Hersteller, Modell, Rang
)
Select Hersteller, Modell, Sum(Rang * 1.0 * Anzahl_Gebote) /
Sum(Anzahl_Gebote) as Klassendurchschnitt
from CTE
Group by Hersteller, Modell
order by Hersteller, Modell;

BTW: Für die Zukunft wäre es wünschenswert ein kleines Repro-Skript von Deiner Seite zu erhalten, in dem bereits einige Beispieldaten und Select-Statements enthalten sind. Dann kann die Abtipperei von Daten aus Screenshots entfallen. Damit erhöhst Du auch die Quote der Antworten...

Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP
www.insidesql.org/blogs/cmu

Answer 3

Hallo Olaf!

in deiner gemachten Abfrage, steht dass Anzahl Gebote eine Ungültige Spalte ist. Die Spalte gibt es auch nicht in der Tabelle. Diese ist ja nur eine Spalte die in der Abfrage ins leben gerufen worde. Ich hab mal versucht es zu modifizieren aber nicht geschafft das Problem zu umgehen.

SELECT COUNT(*) AS AnzahlGebote, 
       Sub.Hersteller
      ,SUB.Modell
      ,SumRang / SumGebote AS Durchschnitt
FROM
   (SELECT Hersteller
          ,Modell
          ,SUM(Rang * AnzahlGebote) AS SumRang
          ,SUM(AnzahlGebote) AS SumGebote
    FROM [DWH].etl.Archive_Autos
    GROUP BY Hersteller
            ,Modell) AS Sub 

AnzahlGebote ist bei mir rot unterstrichen im Managementstudio. Hatte auch versucht in der Funktion SUM die Funktion Count aufzurufen, aber da ensteht auch ein Fehler bei.

---

Kalle

Answer 4

Hallo Kalle,
hast Du meinen Vorschlag auch gesehen? Der sollte das Problem beheben.

Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP
www.insidesql.org/blogs/cmu

Answer 5

Hallo Christoph,

Ich habe mehr als Automarken im System, die Automarken auf dem Bild sind nur ein kleiner Abschnitt. Deswegen ist es sehr viel aufwand das alles manuell auf zu schreiben. 

Ich bekomme auch immer neue Daten die mittels des ETL Prozess in der DB integriert werden.

Answer 6

Hallo Kalle/Wifer,

mal wieder im Doppelpack.

Da hatte ich nicht so ganz genau hingesehen, aber da muss man ja nur Eure ursprüngliche Abfrage einfügen.

SELECT Sub.Hersteller
      ,SUB.Model
      ,SUB.SumRang / SUB.SumGebote AS Durchschnitt
FROM
   (SELECT AGG.Hersteller
          ,AGG.Modell
          ,SUM(AGG.Rang * AGG.AnzahlGebote) AS SumRang
          ,SUM(AGG.AnzahlGebote) AS SumGebote
    FROM 
          (SELECT Hersteller
                 ,Modell
                 ,Rang
                 ,COUNT(*) AS AnzahlGebote
          FROM DWH.etl.Archive_Autos
          GROUP BY Hersteller
                  ,Model
                  ,Rang
          ) AS AGG
    GROUP BY Hersteller
            ,Model
   ) AS Sub

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 7

Ein Repro-Skript sollte die Tabellenstruktur und einige Beispielsätze enthalten, die ausreichen um die aktuelle Fragestellung zu diskutieren. Dies sind keine Produktionsdaten und auch keine Massen-Daten. In der Regel reicht ein Dutzend Datensätze oder weniger aus.

In meiner Antwort war die Lösung unabhängig von den Daten vorhanden. Die Daten waren nur zur Nachvollziehbarkeit für andere Leser gedacht.

Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP
www.insidesql.org/blogs/cmu

Answer 8

Ein "gewichteter Durchschnitt" wird in dem Fall nicht gebraucht:

SELECT Hersteller, Modell, AVG(1.0 * Rang) AS Klassendurchschnitt
FROM @Archive_Autos
GROUP BY Hersteller, Modell
ORDER BY Hersteller, Modell;

Oder habe ich etwas übersehen?

---

The Data Specialist (Blog)

Answer 9

Hallo Olaf!

danke dir sehr Herlzlich für deine Hilfe. Ich würde gerne noch die nachkommar stellen anzeigen lassen, die bei der Division enstehen. Dazu habe ich die funktion Decimal hinzugefügt. 

Komischerweise werden aber immer nullen in den Nachkommarstellen im Ergebnis angezeigt.

PRINT('Durschnittsgebote pro Hersteller - Wo biete ich am besten!?'); 
SELECT Sub.Hersteller
      ,SUB.Modell
      ,CAST( (SUB.SumRang / SUB.SumGebote )  As Decimal(6,2) )  AS Durchschnitt, 
      SUB.SumGebote
FROM
   (SELECT AGG.Hersteller
          ,AGG.Modell
          ,SUM(AGG.Rang * AGG.AnzahlGebote ) AS SumRang
          ,SUM(AGG.AnzahlGebote) AS SumGebote
    FROM 
          (SELECT Hersteller
                 ,Modell
                 ,Rang
                 ,COUNT(*) AS AnzahlGebote
          FROM [DWH].[etl].[Archive_Autos]
          GROUP BY Hersteller
                  ,Modell
                  ,Rang
          ) AS AGG
    GROUP BY Hersteller
            ,Modell
   ) AS Sub  
   ORDER BY Durchschnitt ASC, SumGebote DESC, Hersteller, Modell ASC

---

Kalle

Answer 10

Hallo Kalle,

wenn einen Integer Wert durch einen anderen Integer Wert dividiert, erhält man wieder einen Integer und der hat keine Nachkommastellen, auch wenn Du es in ein Decimal konvertierst. Du muss die Werte vor der Division konvertieren, z.B. indem Du sie per CONVERT/CAST umwandelst.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 11

Hallo Kalle,
in den beiden anderen Lösungsansätzen, war das Integer-Problem bereits gelöst!
Just my two cents.

Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP
www.insidesql.org/blogs/cmu

Answer 12

Hallo Kalle,

falls Du schon auf einen SQL Server 2012 zurückgreifen kannst, dann kommt auch eine Variante mit Window Functions in Frage:

SELECT Hersteller,
	Modell
	Rang,
	COUNT(*) AS [AnzahlGebote],
	(SUM([Rang] * COUNT(*) * 1.0) OVER (PARTITION BY Hersteller, Modell)) / (SUM(COUNT(*)) OVER (PARTITION BY Hersteller, Modell)) AS [Klassendurchschnitt]
FROM [DWH].[etl].[Archive_Autos]
GROUP BY Hersteller, Modell, Rang

Im Gegensatz zu den anderen Varianten erhälst Du hier die gleiche Menge an Ergebniszeilen, wie in Deiner ursprünglichen Abfrage. Das kann gewollt sein oder auch nicht.

HTH
Kostja

---

Please mark as 'Answer', if the solution solves your problem.

Answer 13

Hallo KalleSql,

Ich gehe davon aus, dass die Antworten Dir weitergeholfen haben.
Solltest Du noch "Rückfragen" dazu haben, so gib uns bitte Bescheid.

Grüße,
Robert

---

Robert Breitenhofer, MICROSOFT 
Bitte haben Sie Verständnis dafür, dass im Rahmen dieses Forums, welches auf dem Community-Prinzip „Entwickler helfen Entwickler“ beruht, kein technischer Support geleistet werden kann oder sonst welche garantierten Maßnahmen seitens Microsoft zugesichert werden können.