Summe der Einzelsätze einer Gruppe

[ILEMax]

Ja, danke
eine Lösung um an die Zahl zu kommen haben wir.
Ich war nur über die "Count(*) Over()" zeile gestolpert, und habe das ausprobiert.
Hätte ja sein können das es da mehr gibt.

Danke

[Fuerchau]

Birgitta ist für ihre komplizieren Lösungen bekannt, aber es geht auch einfacher und vor allem auch schneller, denn CTE's sind keine Zwischentabellen:

Generell gilt:

aggr(x) over(partition by ...)

Also:
select f1, f2
, count(*) over(partition by f1, f2) Anzahl
, count(*) over() Summe

Denn Summe von Anzahl = Count über alles.

Ebenso lässt sich errechnen:

,sum(wert) over() Summe
,sum(wert) over(partition by F1, F2) SummeGruppe
,sum(wert) over(partition by F1, F2) * 100 / sum(wert) over() ProzentAnteil

usw. usf.

Einfacher ist es mit einer derived Table:

select a.*, Anzahl*100/Summe ProzentAnteil from (
select f1, f2
, count(*) over(partition by f1, f2) Anzahl
, count(*) over() Summe
from mytable
) a

https://www.ibm.com/docs/en/i/7.4.0?...specifications

[B.Hauser]

Birgitta ist für ihre komplizieren Lösungen bekannt, aber es geht auch einfacher und vor allem auch schneller, denn CTE's sind keine Zwischentabellen:

Baldur, bevor Du behauptest ich würde komplizierte Lösungen liefern.
Probier zunächst mal Deine Lösungen aus! Bei keiner der Lösungen, die du bietest erhälst Du die gewünschten 24!

Select F1, f2, Count(*) Count0, 
       Count(*) over(partition By F1, F2) Count1,
       Count(*) over(Partition by F1) Count2
  from x
  Group By F1, F2;

Dieses Beispiel liefert:

F1  F2  Count0  Count1  Count2
A   B   10      1       2 
A   C   14      1       2

Der Count(*) und auch Count(Spalte) ist jeweils ein Count(Distinct, d.h. Duplikate werden NICHT gezählt.

Und wenn Du schon Prozent rechnen willst und Dir meine Lösungen zu kompliziert sind, dann nimm wenigstens den RATIO_TO_REPORT!

RATIO_TO_REPORT(ColX) OVER(Partition By ColY, ColZ) * 100

Das ist wie beim Äffle und Pferdle:
An der Tafel steht 1*1 -
Äffle schreibt die Lösung = 2 -
Pferdle schüttelt den Kopf -
Äffle korritiert: 1*2 -
Pferdle staunt -
Äffle sagt: 'G'lernt isch halt g'lernt!'

[Fuerchau]

Dies ist definitiv falsch:
"Der Count(*) und auch Count(Spalte) ist jeweils ein Count(Distinct, d.h. Duplikate werden NICHT gezählt."

Count(*) zählt alles
Count(Spalte) zählt alles was nicht NULL ist
Count(distinct Spalte) zählt alles was nicht NULL und eindeutig ist.
Dies gilt schon so lange, wie ich SQL mache.
Dies gilt auch für die over()-Funktionen.

Ein "count(*) over(partition by f1, f2) ... group by f1, f2" macht überhaupt keinen Sinn, da das Ergebnis immer 1 ist.
Bei "count(*) over(partition by f1) ... group by f1, f2" ergibt die Zählung die Anzahl der Gruppen F1, allerdings werden dann wieder alle Zeilen ausgegeben.

Ein count(*) over() zählt immer alle Zeilen des Ergebisses.

Die Frage stellt sich nun, was denn genau gezählt werden soll, in der Regel reicht da ein over() ohne einen finalen Group by, da man die Anzahl bestimmter Vorkommen haben will.

Z.B:
select f1, f2
,count(*) over() Gesamtergebniss
,count(*) over(partition by f1) AnzahlGruppenF1
,count(distinct f2) over() AnzahlEindeutigGesamt
from mytable
group by f1, f2

Und was ist gegen eine klassische Prozentrechnung einzuwenden;-)?

[ILEMax]

Na ja Baldur, ich kenne die Theorie nicht.
Aber in der Praxis stimmt das Ergebnis halt nicht.

[Fuerchau]

Also bisher konnte ich die Ergebnisse immer verifizieren. Gerechnet wurde richtig.
Deine Erwartung mag da eine andere sein.
Wie gesagt, ein over() Ergebnis wird auf jede Zeile innerhalb des Resultsets ausgegeben.
Gerechnet wird also erst nach dem das Resultset komplett ist.

Select F1, f2, Count(*) Count0, 
       Count(*) over(partition By F1, F2) Count1,
       Count(*) over(Partition by F1) Count2
  from x
  Group By F1, F2;

Hier wird gezählt nach der Gruppierung von Group By F1, F2!

Du hast also 10 A/B Zeilen und 14 A/C-Zeilen.
Innerhalb der Gruppe F1/F2 kommt diese Zeile je 1 Mal vor.
Innerhalb der Gruppe F1 kommt A eben 2 mal vor.

F1  F2  Count0  Count1  Count2
A   B   10      1       2 
A   C   14      1       2

Das hat mit distinct hier nichts zu tun, da du ja nur 2 Ergebniszeilen hast.
Lass mal das Group by am Ende weg, so dass du alle 34 Zeilen siehst.
Dabei muss der erste Count0 mit over() ergänzt werden.

Count0 liefert dann 34 über alles.
Count1 liefert dann 10 bei A/B und 14 bei AC.
Count2 liefert dann wieder 34, da du nur A hast.

Und solange man keinen Distinct bei Aggregaten angibt, wird auch nicht distinct gezählt.
Was ist daran nun so schwer zu verstehen?

[ILEMax]

Ja, ggf ist meine ERWARTUNG eine andere.

Es ist gut, wenn du hier SQL Untericht gibst!

Da ich jedoch mein ERWARTUNG in#1 beschrieben habe, ERWARTE ich auch eine Antwort die dazu passt.

Und das war

gibt es so ein nettes Count(*) over() auch in einer Form die mir in obigen Bsp

24 A B 10
24 A C 14

also die Summe von 10 + 14 zurück gibt?

Daher ist in diesem Fall deine Antwort lehrreich aber für mein Problem falsch.

[Fuerchau]

Ich hatte 34 geschrieben, aber 24 (10+14) ist natürlich korrekt.
Das geht aber halt nur, in dem du keinen Group By am Ende machst.
Du hast dann 24 Zeilen, in denen Count0 auf 24 steht.

U.u. errecichst du dein Ziel von 2 Zeilen, in dem du einen "select distinct " kodierst. Dann werden doppelte Zeilen eben entfernt.

Also

24 A B 10
24 A B 10
24 A B 10
24 A B 10
24 A B 10
24 A B 10
24 A B 10
24 A B 10
24 A B 10
24 A B 10
24 A C 14
24 A C 14
24 A C 14
24 A C 14
24 A C 14
24 A C 14
24 A C 14
24 A C 14
24 A C 14
24 A C 14
24 A C 14
24 A C 14
24 A C 14
24 A C 14

Distinct dann:
24 A B 10
24 A C 14

[Fuerchau]

Übrigens geht das auch klassisch:

select * from (
  Select F1, f2, Count(*) Count0, 
    from x
    Group By F1, F2
) a
cross join lateral (
  select count(*) Count1 
   from x
   where a.f1 = x.f1 and a.f2 = x.f2
)

Der Inner group by liefert

10 A B
14 A C

der Join verknüüft einzeln A,B => count 10 und A,C => Count 14

Wenn du lateral join nicht magst, gehts auch mit scalarem subselect:

select a.* 
  , ( select count(*)
        from x
        where a.f1 = x.f1 and a.f2 = x.f2
    ) Count1
from (
  Select F1, f2, Count(*) Count0, 
    from x
    Group By F1, F2
) a

Der join lateral ist wie ein scalarer Subselect zu sehen, der allerdings auch mehr als 1 Spalte liefern kann.

[Andreas_Prouza]

Pass auf, dass du nicht noch bekannt wirst für deine komplizierten Lösungen ;-)

[Fuerchau]

Ja, da ist mir noch eine einfachere Lösung tatsächlich eingefallen:

select * from (
  Select F1, f2, Count(*) Count0, 
    from x
   Group By F1, F2;
)
cross join lateral (
  select count(*) Count1
    from x
)

Der innere Select liefert die Anzahl je Gruppe

A, B, 10
A, C, 14

Der Cross join liefert die Zahl 24 und wird mit jeder Zahl verknüpft, also als Ergebnis
A, B, 10, 24
A, C, 14, 24


Der Join Lateral ist, vor allem in seiner Performance, nicht zu verachten.
Denn dieser bearbeitet den SQL so, dass keine langwierige Optimierung wie bei normelen Joins nötig ist.
Über die Where-Klausel werden die Daten ausschließlich auf die aktuelle Zeile eingeschränkt.

Beispiel:

select * from (
  select f1, f2   -- Liefert nur die Gruppen
    from x
   group by f1, f2
) a
cross join lateral (
  select count(*) Count0   -- Zählt die Ergebnisse der Gruppen f1,f2
    from x
   where a.f1 = x.f1
     and a.f2 = x.f2
)
cross join lateral (
  select count(*) Count1   -- Zählt die Ergebnisse der Gruppen f1
    from x
   where a.f1 = x.f1
)
cross join lateral (
  select count(*) Count2   -- Zählt das Gesamtergebnis
    from x
)

Ich sagte ja schon mal:
Der Join lateral wirkt wie ein scalarer subselect, der allerdings mehr als 1 Spaltenergebnis liefern kann.
In der Kombination mit Values(....) kann man bereits Teilergebnisse berechnen, auf die in folgenden join lateral wieder Bezug genommen werden kann.
Dies vereinfacht z.T. erheblich die SQL's, da sich wiederholende Berechnungen nicht benötigt werden.
Man kann mit dem Ergebnis des lateral wieder einen weiteren Join, eine Where-Klausel oder einen Order by verwenden.