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?

[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