Summe der Einzelsätze einer Gruppe

[ILEMax]

Moin zusammen,

SQL

select f1, f2, count(*) as anz from datei where ... group by F1, f2 order by f1, f2

zeigt

A B 10
A C 14


Wenn ich das erweiter um

select count(*) over () as summe, f1, f2, count(*) as anz from datei where ... group by F1, f2 order by f1, f2

kommt im oberen Bsp
2 A B 10
2 A C 14

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?

NICHT a la "With rollup" als Summe in der Liste sonder wie 'Over()' als summe in der Zeile?

Danke

[Fuerchau]

over(partition by f1, f2)

[ILEMax]

Danke, aber das hat leider nicht funktioniert
mit der Anpassung steht in Summe immer eine 1

[B.Hauser]

Willst Du folgendes Ergebnis?
F1 F2, Anz, Summe
A B 10 24
A B 14 24

Wenn ja, würde ich das mit einer zusätzlichen Common Table Expression lösen:

With x as (Select f1, f2, count(*) as anz
               from x
               Group By F1, F2)
Select F1, f2, Anz, Sum(Anz) Over()
  from x;

[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?

[B.Hauser]

Und ich weiß nicht, warum du die beste Erfindung seit es SQL gibt, den join lateral, immer noch nicht magst.

Weil er in den Beispielen, die du hier anführst völlig überflüssig ist.
Es ist ja nicht so, dass ich den LATERAL nicht verwende, aber nur dann wenn er notwendig ist, z.B. wenn eine Tabellen-Funktion aufgerufen wird und diese als Parameter Werte aus der ersten Tabelle benötigt. In diesem Fall muss LATERAL verwendet werden aber ansonsten nicht.

Ansonsten weiß ich nicht was Du gemessen bzw. verglichen hast. Die folgenden Beispiele liefern alle das gleiche Ergebnis. Die Varianten ohne LATERAL werden identitsch optimiert (ohne Aggregation nur über Hashes), während die Lösung mit Lateral zwei zusätzliche CACHEs und eine Aggregation erfordert, was selbst bei diesen kleinen Daten lt. Visual Explain 0.02 Sekunden kostete.

select x.f1, x.f2
       ,c.*
   from x
 cross Join lateral (Select 
                      Count(*) Count1
                     ,sum(W1) Wert1
                     ,avg(W2) Wert2
                     From x a
                    where a.F1 = x.F1   
                   ) c;

Select x.F1, X.F2, c.*
  from x join (Select F1, Count(*) Count1, Sum(W1) Wert1, Avg(W2) Wert2
                 from x
                 Group by F1) c
               on x.F1 = c.F1;

With c as (Select F1, Count(*) Count1, Sum(W1) Wert1, Avg(W2) Wert2
             from x
             Group By F1)
Select x.F1, X.F2, c.*
  from x join c on x.F1 = c.F1;

[Fuerchau]

Weil ich meist nicht mit kleinen, sondern mit großen Datenmengen arbeite.
Die Vorteile sind nicht zu vernachlässigen und haben mit Table-Function übehaupt nichts zu tun.
Table-Functions lassen sich auch mit normmalen joins verwenden, da man einfach nur "from Table(tablefunction(...))" verwendet. Ein Lateral ist auch hier nicht zangsweise erforderlich.

https://medium.com/@goldengrisha/a-d...n-7b09fcb3b745
https://www.geeksforgeeks.org/sql/la...eyword-in-sql/

Und ein bisschen älter, hatte ich schon mal geposted:
https://www.rpgpgm.com/2021/10/using...al-in-sql.html