PDA

Vollständige Version anzeigen : Alternative zu Schalter-Funktion - Gruppierung


ElBirn
23.10.2007, 12:15
Hallo Leute,

mal wieder ein Problem....

In ein einer Tabelle gibt es das Feld [Emissionsdatum] welches Werte vom 01.01.1997 bis 31.12.2007 enthalten kann. Ich möchte nun ein Gruppierung nach dem Feld Branche machen.

Und zwar so:

SELECT Switch([Emissionsdatum] Is Not Null,"Total",[Emissionsdatum]<#1/1/2001#,"hot",[Emissionsdatum]>=#1/1/2001#,"cold") AS Periode, tbl_Liste_IPOs_vom_14062007.Branche
FROM tbl_Liste_IPOs_vom_14062007 INNER JOIN qry_Emissionspreis_ErsterPreis_Euro ON tbl_Liste_IPOs_vom_14062007.IPO_ID = qry_Emissionspreis_ErsterPreis_Euro.IPO_ID
GROUP BY Switch([Emissionsdatum] Is Not Null,"Total",[Emissionsdatum]<#1/1/2001#,"hot",[Emissionsdatum]>=#1/1/2001#,"cold"), tbl_Liste_IPOs_vom_14062007.Branche;

Das Problem ist, dass ich alle drei Gruppierungsebenen will und das scheint mit Schalter nicht zu gehen. Es kommt nur "Total", die anderen werden dann nicht mehr berücksichtigt.

Ich brauche alle drei Ebenen:
- Total
- Hot
- Cold

Wie könnte ich das lösen?

Vielen Dank.

Lucas

Anne Berg
23.10.2007, 13:14
Wie sollte da auch etwas anderes als "Total" herauskommen?! ;)

Da musst du schon eine anderes Kriterium als "Is Not Null" finden.

ElBirn
23.10.2007, 13:48
Na dann hab ich mich wohl das Problem schlecht ausgedrückt...



Selbst wenn ich was anderes für die "Total" Gruppierungsebene verwende kommt total raus.

Das liegt wohl daran, dass es Schnittmengen sind. Also in Text ausgedrückt brauche ich folgende Gruppierungsebenen:

- Total: jedes Emissionsdatum
- Hot: Emissionsdatum <01.01.2001
- Cold: Emissionsdatum >=01.01.2001

Hot und Cold sind somit Schnittmengen von Total!!!

Anne Berg
23.10.2007, 14:05
Dann versuche folgendes:SELECT Sum(IIF([Emissionsdatum] Is Not Null,1, 0)) AS Total, Sum(IIF([Emissionsdatum]<#1/1/2001#,1,0)) AS hot, Sum(IIF([Emissionsdatum]>=#1/1/2001#,1,0)) AS cold, tbl_Liste_IPOs_vom_14062007.Branche
FROM ...Dann kannst du die Gruppen auszählen.

Hoffe, das hilft dir weiter.

ElBirn
23.10.2007, 15:34
Thanks... Das geht... aber ich brauche die Gruppierungsebenen in den Spalten!

Geht das nicht?

Anne Berg
23.10.2007, 15:39
Dann habe ich dein Problem bzw. deinen Plan wohl noch nicht verstanden.

Willst du es noch einmal versuchen...?
Du kannst nur zwischen hot und cold unterscheiden, das Datum kann nur < oder > sein, nicht beides.

ElBirn
23.10.2007, 16:57
Ok, also...

Es gibt im Feld Emissionsdatum verschieden Werte von 01.01.1997 bis heute und ich brauche eine Gruppierung in Zeilen:

Total: jedes Emissionsdatum
Hot: Emissionsdatum <01.01.2001
Cold: Emissionsdatum >=01.01.2001

Hot und Cold ist kein Problem - siehe Screenshot.

Aber die Zeile für Total bekomme ich nicht hin da Total aus Hot & Cold beiden besteht. (Schnittmenge)

Hier der SQL von dem was ich bisher habe:

SELECT IIf([Emissionsdatum]<#1/1/2001#,"hot","cold") AS a, tbl_Liste_IPOs_vom_14062007.Branche, Avg(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS UP, StDev(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS StAbw, Min(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS [Min], Max(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS [Max], Count(tbl_Liste_IPOs_vom_14062007.ISIN) AS Anzahl, -Sum(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])>0)/Count(tbl_Liste_IPOs_vom_14062007.ISIN) AS [% Positiv]
FROM tbl_Liste_IPOs_vom_14062007 INNER JOIN qry_Emissionspreis_ErsterPreis_Euro ON tbl_Liste_IPOs_vom_14062007.IPO_ID = qry_Emissionspreis_ErsterPreis_Euro.IPO_ID
GROUP BY IIf([Emissionsdatum]<#1/1/2001#,"hot","cold"), tbl_Liste_IPOs_vom_14062007.Branche, tbl_Liste_IPOs_vom_14062007.Delete
HAVING (((tbl_Liste_IPOs_vom_14062007.Delete)=False));



Was ich brauch ist eben noch die Gruppierungsebene TOTAL!!

Jetzt klar wie ich meine?

lalo
23.10.2007, 17:18
Bin erst eben auf diesen Threag gekommen !

Frage: Wo willst Du Total stehen haben ?
Es ist aber wohl nicht die Schnittmenge sondern die Vereinigungsmenge.
Gruß Bernd

ElBirn
23.10.2007, 17:22
In der Zeile, also jede Branche soll nach Total, Cold, Hot gruppiert sein!!!

nicht die Schnittmenge sondern die Vereinigungsmenge.

Hast Recht :-)

lalo
23.10.2007, 19:06
Hallo,
hier ein Versuch, Tippfehler sind nicht ausgeschlossen.

Prinzip:

In einem Subselect mit 2 Unions werden die Daten zusammengestellt
1. alle Werte Cold, die Spalten für hot und total sind konstant 0
2. alle Werte für Hot, die Spalten für Cold und Total sind konstant 0
3. alle Werte für Total, die Spalten für Cold und Hot sin konstant 0, hier ist keine Abfrage auf das Datum drinnen.

Und das ganze wird im äußeren Select summiert.

Das Kriterium "...Delete = false" würde ich in die Where Klausel nehmen, da es sich auf die Zeilen der Tabellen bezieht und nicht auf das Ergebnis der Aggregation wofür eigentlich HAVING da ist (aber das nur am Rande)

Gruß Bernd

select branche,
sum(ColdUp), sum(ColdStAbw), sum(ColdMin), sum(ColdMAx), sum(ColdAnzahl), sum(ColdPrzPositiv),
sum(hotup), sum(hotStAbw), sum(hotMin), sum(hotMax), sum(hotAnzahl), sum(hotPrzPositiv),
sum(TotalUp), sum(totalStAbw), sum(totalMin), sum(TotalMax), sum(TotalAnzahl), sum(Total(PrzPositiv)
from
(
SELECT
tbl_Liste_IPOs_vom_14062007.Branche,
Avg(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS ColdUP,
StDev(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS ColdStAbw,
Min(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS ColdMin,
Max(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS ColdMax,
Count(tbl_Liste_IPOs_vom_14062007.ISIN) AS ColdAnzahl,
-Sum(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])>0)/Count(tbl_Liste_IPOs_vom_14062007.ISIN) AS ColdPrzPositiv,
0 as HotUp, 0 as HotSAbw, 0 as ColdMin, 0 as ColdMax, 0 as ColdAnzahl,
0 as TotalUp, 0 as TotalStAbw, 0 as TotalMin, 0 as TotalMax, 0 as TotalAnzahl
FROM tbl_Liste_IPOs_vom_14062007 INNER JOIN qry_Emissionspreis_ErsterPreis_Euro ON tbl_Liste_IPOs_vom_14062007.IPO_ID = qry_Emissionspreis_ErsterPreis_Euro.IPO_ID
where Emissionsdatum <#1/1/2001#
GROUP BY tbl_Liste_IPOs_vom_14062007.Branche, tbl_Liste_IPOs_vom_14062007.Delete
HAVING (((tbl_Liste_IPOs_vom_14062007.Delete)=False))
union
SELECT
tbl_Liste_IPOs_vom_14062007.Branche,
0,0,0,0,0,0,
Avg(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS hotUP,
StDev(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS hotStAbw,
Min(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS hotMin,
Max(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS hotMax,
Count(tbl_Liste_IPOs_vom_14062007.ISIN) AS hotAnzahl,
-Sum(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])>0)/Count(tbl_Liste_IPOs_vom_14062007.ISIN) AS hotPrzPositiv,
0, 0, 0, 0, 0, 0
FROM tbl_Liste_IPOs_vom_14062007 INNER JOIN qry_Emissionspreis_ErsterPreis_Euro ON tbl_Liste_IPOs_vom_14062007.IPO_ID = qry_Emissionspreis_ErsterPreis_Euro.IPO_ID
where not Emissionsdatum <#1/1/2001#
GROUP BY tbl_Liste_IPOs_vom_14062007.Branche, tbl_Liste_IPOs_vom_14062007.Delete
HAVING (((tbl_Liste_IPOs_vom_14062007.Delete)=False))
union
select tbl_Liste_IPOs_vom_14062007.Branche,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
Avg(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS hotUP,
StDev(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS hotStAbw,
Min(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS hotMin,
Max(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS hotMax,
Count(tbl_Liste_IPOs_vom_14062007.ISIN) AS hotAnzahl,
-Sum(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])>0)/Count(tbl_Liste_IPOs_vom_14062007.ISIN) AS hotPrzPositiv
FROM tbl_Liste_IPOs_vom_14062007 INNER JOIN qry_Emissionspreis_ErsterPreis_Euro ON tbl_Liste_IPOs_vom_14062007.IPO_ID = qry_Emissionspreis_ErsterPreis_Euro.IPO_ID
GROUP BY tbl_Liste_IPOs_vom_14062007.Branche, tbl_Liste_IPOs_vom_14062007.Delete
HAVING (((tbl_Liste_IPOs_vom_14062007.Delete)=False))
) as SubSelect
group by branche

ElBirn
23.10.2007, 22:01
Hilfe .... das ist ja ein mörderischer SQL... :boah:

Ich versteh nichts davon... Ich hab aber versucht einfach den SQL in eine leere Abfrage zu kopieren, es kommt aber eine Syntaxfehlermeldung:

"fehlender Operator in Abfrageausdruck"

Ich weiss leider nicht woran das liegen kann... Help me!!!

Arne Dieckmann
24.10.2007, 06:54
Mörderisch sind auf jeden Fall Feldnamen wie "Delete", die man unbedingt in eckige Klammern setzen sollte. Außerdem sieht's hier ein wenig kribbelig aus:
... sum(TotalAnzahl), sum(Total(PrzPositiv)
from
(
SELECT Sieht aus, als ob bei der roten Markierung eine Klammer zu viel ist. Mehr ist mir bisher nicht aufgefallen (die Logik will ich nicht checken).

lalo
24.10.2007, 08:58
Hallo,

testen konnte ich das ganze natürlich nicht !

Zum Verständnis / Logik:
Die Subselects enthalten je 6 Spalten für "COLD", "HOT" und "TOTAL" sowie zuerst die Branche.

Im ersten Subselect werden die Daten "COLD" selektiert (aus Deinem SQL kopiert und nur die as.. geändert) und die restlichen Spalten für HOT und für TOTAL auf konstant 0 gestellt.

Beim 2. Subselect werden die Spalten für "COLD" auf 0 gesetzt, dann die Spalten für HOT selektiert und die Spalten für TOTAl auf 0 gesetzt.

Und beim 3. Subselect analog, so dass dort die Spalten TOTAL mit Werte gefüllt werden und die für COLD und HOT 0 sind.

Durch die Verbindung mit UNION wird nun SQL-Intern ein Dataset erzeugt, der alle so generierten Zeilen enthält. Und über diesen - für uns nicht sichtbaren Dataset erfolgt nun die Summierung, wobei als Gruppierung die Branche angegeben wird.

Je Brance entstehen so 3 interne Datenzeilen die dann Summiert werden.

Beispiel zum Prinzip:

Pharma, 6, 5, 4, 3, 2, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 - Zeile mit Coldwerten
Pharma, 0, 0, 0, 0, 0, 0, 7, 6, 5, 4, 3, 2, 0, 0, 0, 0, 0, 0 - Zeile mit Hotwerten
Pharma, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 8, 7, 6, 5, 4, 3 -Zeile mit Totalwerten
und durch den äußeren Select erfolgt die Summierung auf den Gruppenbegriff Branche zum Endergebnis:
Pharma, 6, 5, 4, 3, 2, 1, 7, 6 ,5, 4 3, 2, 8, 7, 6, 5, 4, 3

Gruß Bernd

Anne Berg
24.10.2007, 10:50
Hallo,

die Idee mit der Union-Abfrage ist schonmal gut, doch geht das sicher auch einfacher.

Versuche mal folgendes:SELECT IIf([Emissionsdatum]<#1/1/2001#,"hot","cold") AS a, tbl_Liste_IPOs_vom_14062007.Branche, Avg(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS UP, StDev(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS StAbw, Min(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS [Min], Max(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS [Max], Count(tbl_Liste_IPOs_vom_14062007.ISIN) AS Anzahl, -Sum(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])>0)/Count(tbl_Liste_IPOs_vom_14062007.ISIN) AS [% Positiv]
FROM
tbl_Liste_IPOs_vom_14062007 INNER JOIN qry_Emissionspreis_ErsterPreis_Euro ON tbl_Liste_IPOs_vom_14062007.IPO_ID = qry_Emissionspreis_ErsterPreis_Euro.IPO_ID
WHERE (((tbl_Liste_IPOs_vom_14062007.Delete)=False));
UNION
SELECT IIf([Emissionsdatum] Not Null,"total","leer") AS a, tbl_Liste_IPOs_vom_14062007.Branche, Avg(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS UP, StDev(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS StAbw, Min(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS [Min], Max(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS [Max], Count(tbl_Liste_IPOs_vom_14062007.ISIN) AS Anzahl, -Sum(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])>0)/Count(tbl_Liste_IPOs_vom_14062007.ISIN) AS [% Positiv]
FROM tbl_Liste_IPOs_vom_14062007 INNER JOIN qry_Emissionspreis_ErsterPreis_Euro ON tbl_Liste_IPOs_vom_14062007.IPO_ID = qry_Emissionspreis_ErsterPreis_Euro.IPO_ID
WHERE (((tbl_Liste_IPOs_vom_14062007.Delete)=False));
Diese Abfrage kannst du zunächst einmal speichern und eine weitere darauf aufsetzen, um die "leer"-Sätze rauszufiltern und zu gruppieren.

ElBirn
24.10.2007, 11:28
Danke euch... Leider gehen aber beiden Vorschläge nicht.

Lösung Bernd:
Fehler:
Doppelter Alias Name: ColdMin

Lösung Anne:
Fehler:
IIf([Emissionsdatum]<#1/1/2001#,"hot","cold") ... wird als Aggregatfunktion nicht eingeschlossen !!

Leider kenne ich mich mit UnionAbfragen überhaupt nicht aus, Sorry!

Anne Berg
24.10.2007, 11:56
Sorry, ich hatte übersehen, dass die Abfragen gruppiert werden müssen wegen der Funktionen. Dann füge doch das GROUP BY jeweils wieder ein. Oder mache die Berechnungen in der zweiten Abfrage.

ElBirn
24.10.2007, 12:04
Dann füge doch das GROUP BY jeweils wieder ein.

Was meinst du mit jeweils? An welcher Stelle?

Meinst du diese Zeile?
GROUP BY IIf([Emissionsdatum]<#1/1/2001#,"hot","cold"), tbl_Liste_IPOs_vom_14062007.Branche, tbl_Liste_IPOs_vom_14062007.Delete

Anne Berg
24.10.2007, 12:42
Hier nochmal der komplette Code der Basisabfrage:SELECT IIf([Emissionsdatum]<#1/1/2001#,"hot","cold") AS a, tbl_Liste_IPOs_vom_14062007.Branche, Avg(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS UP, StDev(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS StAbw, Min(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS [Min], Max(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS [Max], Count(tbl_Liste_IPOs_vom_14062007.ISIN) AS Anzahl, -Sum(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])>0)/Count(tbl_Liste_IPOs_vom_14062007.ISIN) AS [% Positiv]
FROM tbl_Liste_IPOs_vom_14062007 INNER JOIN qry_Emissionspreis_ErsterPreis_Euro ON tbl_Liste_IPOs_vom_14062007.IPO_ID = qry_Emissionspreis_ErsterPreis_Euro.IPO_ID
WHERE (((tbl_Liste_IPOs_vom_14062007.Delete)=False))
GROUP BY IIf([Emissionsdatum]<#1/1/2001#,"hot","cold"), tbl_Liste_IPOs_vom_14062007.Branche;
UNION
SELECT IIf([Emissionsdatum] Not Null,"total","leer") AS a, tbl_Liste_IPOs_vom_14062007.Branche, Avg(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS UP, StDev(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS StAbw, Min(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS [Min], Max(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS [Max], Count(tbl_Liste_IPOs_vom_14062007.ISIN) AS Anzahl, -Sum(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])>0)/Count(tbl_Liste_IPOs_vom_14062007.ISIN) AS [% Positiv]
FROM tbl_Liste_IPOs_vom_14062007 INNER JOIN qry_Emissionspreis_ErsterPreis_Euro ON tbl_Liste_IPOs_vom_14062007.IPO_ID = qry_Emissionspreis_ErsterPreis_Euro.IPO_ID
WHERE (((tbl_Liste_IPOs_vom_14062007.Delete)=False))
GROUP BY IIf([Emissionsdatum] Not Null,"total","leer"), tbl_Liste_IPOs_vom_14062007.Branche;

lalo
24.10.2007, 16:10
Hallo,

Anne, Dein Vorschlag führt aber dazu, dass die Daten untereinander in einzelnen Ergebniszeilen stehen, ich habe aber verstanden, dass er die Daten zu einer Branche in einer Ergebniszeile will.

Auf die Frage "Wo willst Du Total stehen haben" kam als Antwort "In der Zeile, also jede Branche soll nach Total, Cold, Hot gruppiert sein"

Gruß Bernd

Anne Berg
24.10.2007, 16:31
Ja, und das stelle ich mir so vor, wie es in Beitrag #7 zu sehen ist.

lalo
24.10.2007, 16:48
Nichts für ungut, dann solls eben so sein.
Freudliche Grüße Bernd

ElBirn
24.10.2007, 17:06
Hallo zusammen,

das Ergebnis soll schon so sein wie Anne es verstanden hat. Was Bernd verstanden hat kann ich leider nicht nachvollziehen.

Ein Problem gibt es noch: Wie komme ich in die Entwurfsansicht? Muss noch die Felder anpassen!!

Danke

Lucas

Anne Berg
24.10.2007, 17:33
Welche Entwurfansicht? Union-Abfragen kann man nur im SQL-Fenster bearbeiten.

Wenn es gar nicht anders geht, bearbeite jeweils die einzelnen Selects im Entwurfsfenster und führe den SQL-Code anschließend wieder mit UNION zusammen.

ElBirn
24.10.2007, 19:30
Ach so, wusste ich nicht.

Ich hab versucht, die einzelnen Selects zu bearbeiten aber ich konnte meine Probleme damit nicht lösen.

Ich habe zwei Probleme:

1. Die Branche wird als Primärschlüssel angezeigt mit der jeweiligen ID. Was ich brauch ist aber aus tbl_Branchen das Feld Branche. Die Id ist nur eine Zahl.

2. Müsste ich einige Felder mit verschiedenen Zahlenformaten belegen, z.B. %

Ich vermute mal, dass sich Problem 2 nicht lösen lässt, aber Problem1 sollte doch gehen, oder?

Ich habs versucht, aber leider hab ich es nicht geschaft, es kamen immer wieder Fehler.

Kann mir bitte noch jemand helfen.

Danke

ElBirn
29.10.2007, 10:07
Hallo Leute,

also die oben genannten Probleme hab ich mittelerweile selbst in den Griff bekommen.

Eine Sache bekomme ich aber nicht hin. Der SQL ist mittlerweile folgender:

SELECT
IIf([Emissionsdatum]<#1/1/2001#,"hot","cold") AS Zeitraum,
tbl_Branchen.Branche, Avg(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS UP,
StDev(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS StAbw,
Min(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS [Min],
Max(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS [Max],
Count(tbl_Liste_IPOs_vom_14062007.ISIN) AS Anzahl,
-Sum(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])>0)/Count(tbl_Liste_IPOs_vom_14062007.ISIN) AS [% Positiv],
Avg([Platzierungsvolumen in Stück exkl Greenshoe]*[Emissionspreis_Euro]) AS DEMV

FROM
(tbl_Branchen INNER JOIN tbl_Liste_IPOs_vom_14062007 ON tbl_Branchen.BR_ID = tbl_Liste_IPOs_vom_14062007.Branche) INNER JOIN qry_Emissionspreis_ErsterPreis_Euro ON tbl_Liste_IPOs_vom_14062007.IPO_ID = qry_Emissionspreis_ErsterPreis_Euro.IPO_ID

WHERE
(((tbl_Liste_IPOs_vom_14062007.Delete)=False))

GROUP BY
IIf([Emissionsdatum]<#1/1/2001#,"hot","cold"),
tbl_Branchen.Branche;

UNION SELECT
IIf([Emissionsdatum] Is Not Null,"total","leer") AS Period,
tbl_Branchen.Branche, Avg(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS UP,
StDev(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS StAbw,
Min(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS [Min],
Max(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS [Max],
Count(tbl_Liste_IPOs_vom_14062007.ISIN) AS Anzahl,
-Sum(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])>0)/Count(tbl_Liste_IPOs_vom_14062007.ISIN) AS [% Positiv],
Avg([Platzierungsvolumen in Stück exkl Greenshoe]*[Emissionspreis_Euro]) AS Zeitraum

FROM
(tbl_Branchen INNER JOIN tbl_Liste_IPOs_vom_14062007 ON tbl_Branchen.BR_ID = tbl_Liste_IPOs_vom_14062007.Branche)
INNER JOIN
qry_Emissionspreis_ErsterPreis_Euro ON tbl_Liste_IPOs_vom_14062007.IPO_ID = qry_Emissionspreis_ErsterPreis_Euro.IPO_ID

WHERE
(((tbl_Liste_IPOs_vom_14062007.Delete)=False))

GROUP BY IIf([Emissionsdatum] Is Not Null,"total","leer"), tbl_Branchen.Branche;


Was ich gerne möchte ist das für jede Branche die Zeilen

- cold
- hot
- total

angezeigt werden. Total ist per Definition schon immer vorhanden. Nun gibt es aber bei einigen Branchen kein Emissionsdatum welches unter hot oder cold fällt, dann die Zeile auch nicht geschrieben... Genau das würde ich aber gerne ändern. Also wenn keine Werte vorhanden sind für cold oder hot soll die Zeile auftauchen.

Habt ihr einen Tip?

Danke

Lucas

P.S.
...im Screenshot sind man das Problem bei den ersten drei Branchen.

Anne Berg
29.10.2007, 15:38
Hallo,

so spontan fällt mir dazu nur ein, die Union-Abfrage entsprechend zu erweitern:

...
Union
Select "hot" As Zeitraum, ... alles Nullen/Leerfelder ...
From ...
Union
Select "cold" ... (s.oben)

ElBirn
29.10.2007, 18:22
Hallo Anne,

vielen Dank für deinen Tip. Ich verstehe aber nicht ganz was du meinst. Könntest du es bitte etwas genauer erklären.

Danke.

Lucas

Anne Berg
29.10.2007, 18:35
Hallo,

ich meinte, du solltest einfach zu jeder Branche je eine Leerzeile mit "hot" und "cold" hinzufügen. Insofern ist mein erster Vorschlag folgendermaßen zu korrigieren: ... (bisheriger SQL-Code)
UNION
Select "hot" As Zeitraum, tbl_Branchen.Branche, ... (weitere Felder, "" oder 0 je nach Datentyp)
From tbl_Branchen
UNION
Select "cold", tbl_Branchen.Branche, ... (weitere Felder, "" oder 0 je nach Datentyp)
From tbl_Branchen
Hoffe, das war jetzt verständlicher.

PS: Wenn du die Gruppierung in einer zweiten Abfrage durchführst, verschwinden die eingefügten Leerzeilen, sobald weitere Daten zu dieser Gruppe vorhanden sind.

ElBirn
29.10.2007, 18:58
:mad:

Tut mir echt leid ich bekomms nicht hin. Könntest du mir bitte den vollständigen SQL mal zeigen.

Anne Berg
29.10.2007, 22:42
Nein, das ist jetzt nicht dein Ernst, oder? :confused:

Was ist daran nicht zu verstehen, was klappt nicht bei dir? Es muss doch irgendwelche Fehlermeldungen geb, die du uns zitieren könntest. Oder du zeigst einen Screenshot, von dem was du siehst, wenn "es" nicht klappt. Irgendetwas musst du schon zur Lösung beitragen.

Nachtrag:
Die einzelnen Selects einer Union-Abfrage müssen stets die gleiche Anzahl Felder haben. Lücken müssen demnach mit Platzhaltern gefüllt werden, also 0 oder "", abhängig vom jeweiligen Datentyp.

Lässt du die Funktionen (SUM, Min, Max etc.) mal raus und übernimmst nur die Felder, die für diese Berechnungen nötig sind, so kann die Gruppierung in der Union-Abfrage entfallen. Dafür erstellst du eine neue Abfrage, die auf der Union-Abfrage basiert, und führst dort die gewünschten Berechnungen und Gruppierungen aus. Dann sollte es passen!

ElBirn
30.10.2007, 00:48
Ich weiß doch auch nicht woran es liegt...

Ich habe es so versucht --> Meldung: Aggregatfunktion schließt IIf([Emissionsdatum]<#1/1/2001#,"hot","leer") .. nicht ein.

[CODE]
SELECT
IIf([Emissionsdatum]<#1/1/2001#,"hot","leer") AS Zeitraum,
tbl_Branchen.Branche, Avg(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS UP, StDev(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS StAbw, Min(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS [Min], Max(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS [Max], Count(tbl_Liste_IPOs_vom_14062007.ISIN) AS Anzahl, -Sum(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])>0)/Count(tbl_Liste_IPOs_vom_14062007.ISIN) AS [% Positiv], Avg([Platzierungsvolumen in Stück exkl Greenshoe]*[Emissionspreis_Euro]) AS DEMV
FROM tbl_Branchen INNER JOIN (tbl_Liste_IPOs_vom_14062007 INNER JOIN qry_Emissionspreis_ErsterPreis_Euro ON tbl_Liste_IPOs_vom_14062007.IPO_ID=qry_Emissionspreis_ErsterPreis_Euro.IPO_ID) ON tbl_Branchen.BR_ID=tbl_Liste_IPOs_vom_14062007.Branche
WHERE (((tbl_Liste_IPOs_vom_14062007.Delete)=False))
GROUP BY IIf([Emissionsdatum]<#1/1/2001#,"hot",""), tbl_Branchen.Branche;

UNION SELECT
IIf([Emissionsdatum] >#1/1/2001#,"cold","leer") AS Period,
tbl_Branchen.Branche, Avg(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS UP,
StDev(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS StAbw,
Min(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS [Min],
Max(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS [Max],
Count(tbl_Liste_IPOs_vom_14062007.ISIN) AS Anzahl,
-Sum(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])>0)/Count(tbl_Liste_IPOs_vom_14062007.ISIN) AS [% Positiv],
Avg([Platzierungsvolumen in Stück exkl Greenshoe]*[Emissionspreis_Euro]) AS Zeitraum

FROM
(tbl_Branchen INNER JOIN tbl_Liste_IPOs_vom_14062007 ON tbl_Branchen.BR_ID = tbl_Liste_IPOs_vom_14062007.Branche)
INNER JOIN
qry_Emissionspreis_ErsterPreis_Euro ON tbl_Liste_IPOs_vom_14062007.IPO_ID = qry_Emissionspreis_ErsterPreis_Euro.IPO_ID

WHERE
(((tbl_Liste_IPOs_vom_14062007.Delete)=False))

GROUP BY IIf([Emissionsdatum] Is Not Null,"cold","leer"), tbl_Branchen.Branche;

UNION SELECT
IIf([Emissionsdatum] Is Not Null,"total","leer") AS Period22,
tbl_Branchen.Branche, Avg(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS UP,
StDev(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS StAbw,
Min(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS [Min],
Max(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])/[Emissionspreis_unbereinigt]) AS [Max],
Count(tbl_Liste_IPOs_vom_14062007.ISIN) AS Anzahl,
-Sum(([Erster_Preis_unbereinigt]-[Emissionspreis_unbereinigt])>0)/Count(tbl_Liste_IPOs_vom_14062007.ISIN) AS [% Positiv],
Avg([Platzierungsvolumen in Stück exkl Greenshoe]*[Emissionspreis_Euro]) AS Zeitraum

FROM
(tbl_Branchen INNER JOIN tbl_Liste_IPOs_vom_14062007 ON tbl_Branchen.BR_ID = tbl_Liste_IPOs_vom_14062007.Branche)
INNER JOIN
qry_Emissionspreis_ErsterPreis_Euro ON tbl_Liste_IPOs_vom_14062007.IPO_ID = qry_Emissionspreis_ErsterPreis_Euro.IPO_ID

WHERE
(((tbl_Liste_IPOs_vom_14062007.Delete)=False))

GROUP BY IIf([Emissionsdatum] Is Not Null,"total","leer"), tbl_Branchen.Branche;
[/CODE]

Dann habe ich es ohne die Funktionen versucht und zwar so:

[CODE]
SELECT IIf([Emissionsdatum]>=#1/1/2001#,"cold","hot") AS Zeitraum, tbl_Branchen.Branche, qry_Emissionspreis_ErsterPreis_Euro.Emissionspreis_Euro, qry_Emissionspreis_ErsterPreis_Euro.Erster_Preis_Euro
FROM tbl_Branchen INNER JOIN (tbl_Liste_IPOs_vom_14062007 INNER JOIN qry_Emissionspreis_ErsterPreis_Euro ON tbl_Liste_IPOs_vom_14062007.IPO_ID = qry_Emissionspreis_ErsterPreis_Euro.IPO_ID) ON tbl_Branchen.BR_ID = tbl_Liste_IPOs_vom_14062007.Branche
WHERE (((tbl_Liste_IPOs_vom_14062007.Delete)=False));

UNION SELECT IIf([Emissionsdatum]<#1/1/2001#,"hot","cold") AS Zeitraum, tbl_Branchen.Branche, qry_Emissionspreis_ErsterPreis_Euro.Emissionspreis_Euro, qry_Emissionspreis_ErsterPreis_Euro.Erster_Preis_Euro
FROM tbl_Branchen INNER JOIN (tbl_Liste_IPOs_vom_14062007 INNER JOIN qry_Emissionspreis_ErsterPreis_Euro ON tbl_Liste_IPOs_vom_14062007.IPO_ID = qry_Emissionspreis_ErsterPreis_Euro.IPO_ID) ON tbl_Branchen.BR_ID = tbl_Liste_IPOs_vom_14062007.Branche
WHERE (((tbl_Liste_IPOs_vom_14062007.Delete)=False));

UNION SELECT IIf([Emissionsdatum] Is Not Null,"total","leer") AS Zeitraum, tbl_Branchen.Branche, qry_Emissionspreis_ErsterPreis_Euro.Emissionspreis_Euro, qry_Emissionspreis_ErsterPreis_Euro.Erster_Preis_Euro
FROM tbl_Branchen INNER JOIN (tbl_Liste_IPOs_vom_14062007 INNER JOIN qry_Emissionspreis_ErsterPreis_Euro ON tbl_Liste_IPOs_vom_14062007.IPO_ID = qry_Emissionspreis_ErsterPreis_Euro.IPO_ID) ON tbl_Branchen.BR_ID = tbl_Liste_IPOs_vom_14062007.Branche
WHERE (((tbl_Liste_IPOs_vom_14062007.Delete)=False));

und diese Abfrage dann in einer anderen eingebunden bei der ich dann die Gruppierung vornehme:

SELECT Abfrage1.Zeitraum, Abfrage1.Branche, Count(Abfrage1.Branche) AS AnzahlvonBranche
FROM Abfrage1
GROUP BY Abfrage1.Zeitraum, Abfrage1.Branche;
[/CODE]

Aber es geht nicht !!!!!! Es kommt genau das Ergebnis wie vorher, selbst wenn ich in der If-Funktion statt "" was anderes schreibe.

Ich werd noch verrückt!

Ich habe die DB hochgeladen bitteeeeeeeee schau mal rein!!!

Anne Berg
30.10.2007, 08:43
So sollte es aber doch machbar sein:
Lässt du die Funktionen (SUM, Min, Max etc.) mal raus und übernimmst nur die Felder, die für diese Berechnungen nötig sind, so kann die Gruppierung in der Union-Abfrage entfallen. Dafür erstellst du eine neue Abfrage, die auf der Union-Abfrage basiert, und führst dort die gewünschten Berechnungen und Gruppierungen aus. Dann sollte es passen!

ElBirn
30.10.2007, 11:31
Genau so habe ich es doch auch schon versucht.

---> Siehe Bsp im Anhang und der zweite Code oben - beides in #31

Anne Berg
30.10.2007, 12:09
Und was hast du mit meinem Vorschlag aus Beitrag #28 gemacht? :confused:

Anne Berg
30.10.2007, 12:27
Hier noch einmal beide Abfragen, so wie ich mir das vorgestellt habe:SELECT IIf([Emissionsdatum]>=#1/1/2001#,"cold","hot") AS Zeitraum, tbl_Branchen.Branche, qry_Emissionspreis_ErsterPreis_Euro.Emissionspreis_Euro, qry_Emissionspreis_ErsterPreis_Euro.Erster_Preis_Euro
FROM tbl_Branchen INNER JOIN (tbl_Liste_IPOs_vom_14062007 INNER JOIN qry_Emissionspreis_ErsterPreis_Euro ON tbl_Liste_IPOs_vom_14062007.IPO_ID = qry_Emissionspreis_ErsterPreis_Euro.IPO_ID) ON tbl_Branchen.BR_ID = tbl_Liste_IPOs_vom_14062007.Branche
WHERE (((tbl_Liste_IPOs_vom_14062007.Delete)=False));

UNION SELECT "hot", tbl_Branchen.Branche, 0, 0
FROM tbl_Branchen ;

UNION SELECT "cold", tbl_Branchen.Branche, 0, 0
FROM tbl_Branchen ;

UNION SELECT IIf([Emissionsdatum] Is Not Null,"total","leer"), tbl_Branchen.Branche, qry_Emissionspreis_ErsterPreis_Euro.Emissionspreis_Euro, qry_Emissionspreis_ErsterPreis_Euro.Erster_Preis_Euro
FROM tbl_Branchen INNER JOIN (tbl_Liste_IPOs_vom_14062007 INNER JOIN qry_Emissionspreis_ErsterPreis_Euro ON tbl_Liste_IPOs_vom_14062007.IPO_ID = qry_Emissionspreis_ErsterPreis_Euro.IPO_ID) ON tbl_Branchen.BR_ID = tbl_Liste_IPOs_vom_14062007.Branche
WHERE (((tbl_Liste_IPOs_vom_14062007.Delete)=False));
SELECT ohne_Fkt.Zeitraum, ohne_Fkt.Branche, Sum(IIf([emissionspreis_euro]>0,1,0)) AS anzBranche
FROM ohne_Fkt
GROUP BY ohne_Fkt.Zeitraum, ohne_Fkt.Branche
ORDER BY ohne_Fkt.Branche, ohne_Fkt.Zeitraum;

ElBirn
30.10.2007, 15:55
Hallo Anne,

das war mal wieder eine schwere Geburt. Na ja, dass war auch meine erste Union-Abfrage.

Ich hab jetzt alle Funktionen eingefügt und angepasst und es funktioniert. Ich musste sogar noch eine weitere Abfrage machen um auf das Ergebnis so zu kommen wie ich es brauche.

Der Grund warum ich die ganze Zeit nicht auf die Lösung gekommen bin, obwohl du mir jede Menge Tips gegeben hast, war die Sache mit den 0, 0, -Spalten und dann in der nächten Abfrage summieren oder eben die Funktion anwenden wenn nicht = 0.

Na ja, .... nach Stunden hin und her hat es zum Schluß doch noch geklappt und ich habe auch das handling mit einer Union-Abfrage gelernt!

:-)

Thanks!

Gruß

Lucas