Top-Themen

AppleEntwicklungHardwareInternetLinuxMicrosoftMultimediaNetzwerkeOff TopicSicherheitSonstige SystemeVirtualisierungWeiterbildungZusammenarbeit

Aktuelle Themen

Administrator.de FeedbackApache ServerAppleAssemblerAudioAusbildungAuslandBackupBasicBatch & ShellBenchmarksBibliotheken & ToolkitsBlogsCloud-DiensteClusterCMSCPU, RAM, MainboardsCSSC und C++DatenbankenDatenschutzDebianDigitiales FernsehenDNSDrucker und ScannerDSL, VDSLE-BooksE-BusinessE-MailEntwicklungErkennung und -AbwehrExchange ServerFestplatten, SSD, RaidFirewallFlatratesGoogle AndroidGrafikGrafikkarten & MonitoreGroupwareHardwareHosting & HousingHTMLHumor (lol)Hyper-VIconsIDE & EditorenInformationsdiensteInstallationInstant MessagingInternetInternet DomäneniOSISDN & AnaloganschlüsseiTunesJavaJavaScriptKiXtartKVMLAN, WAN, WirelessLinuxLinux DesktopLinux NetzwerkLinux ToolsLinux UserverwaltungLizenzierungMac OS XMicrosoftMicrosoft OfficeMikroTik RouterOSMonitoringMultimediaMultimedia & ZubehörNetzwerkeNetzwerkgrundlagenNetzwerkmanagementNetzwerkprotokolleNotebook & ZubehörNovell NetwareOff TopicOpenOffice, LibreOfficeOutlook & MailPapierkorbPascal und DelphiPeripheriegerätePerlPHPPythonRechtliche FragenRedHat, CentOS, FedoraRouter & RoutingSambaSAN, NAS, DASSchriftartenSchulung & TrainingSEOServerServer-HardwareSicherheitSicherheits-ToolsSicherheitsgrundlagenSolarisSonstige SystemeSoziale NetzwerkeSpeicherkartenStudentenjobs & PraktikumSuche ProjektpartnerSuseSwitche und HubsTipps & TricksTK-Netze & GeräteUbuntuUMTS, EDGE & GPRSUtilitiesVB for ApplicationsVerschlüsselung & ZertifikateVideo & StreamingViren und TrojanerVirtualisierungVisual StudioVmwareVoice over IPWebbrowserWebentwicklungWeiterbildungWindows 7Windows 8Windows 10Windows InstallationWindows MobileWindows NetzwerkWindows ServerWindows SystemdateienWindows ToolsWindows UpdateWindows UserverwaltungWindows VistaWindows XPXenserverXMLZusammenarbeit
GELÖST

MySQL Abfrage artikelbezogener Umsatz pro Jahr ?

Frage Entwicklung Datenbanken

Mitglied: Whirly

Whirly (Level 1) - Jetzt verbinden

17.05.2011 um 10:46 Uhr, 3868 Aufrufe, 5 Kommentare

Hallo liebe "Helfergemeinde"

Unser Warenwirtschaftssystem basiert auf einer MySQL Datenbank. (5.0.51b)

Da dieses Programm jedoch auswertungstechnisch nicht sehr viel hergibt mache ich viele Spezialauswertungen (Umsatzstatistiken, Lagerstückzahlen,...) im Excel (VBA)

Nun zu meinem Problem .
In der Tabelle "fpositionen" werden alle Artikel aufgeführt die verkauft werden. (Verkaufsdatum-Artikelnummer-Artikelname-Gesamtpreis-Menge)
Das heißt: Jedes mal wenn ein Artikel verkauft wird, wird eine neue Zeile eingefügt. Aus dieser Tabelle möchte ich nun eine Abrfage generieren.

Folgende Abfrage habe ich schon: (vereinfacht auf mein Grundproblem)
SELECT `Artikelname`, SUM(`Gesamtpreis`), SUM(`Menge`)
FROM `mand9`.`fpositionen`
GROUP BY `Artikelnummer`
Das ergibt ja folgendes Abfrageergebnis: Artikelname - Summe des Gesamtpreises - Summe der Menge

Nun möchte ich aber folgendes Abfrageergebnis:
Artikelname - Summe des Gesamtpreises - Summe der Menge - Summe des Gesamtpreises im Jahr 2008 - Summe der Menge im Jahr 2008 - Summe des Gesamtpreises im Jahr 2009 - Summe der Menge im Jahr 2009 - . . .

Mit dem Zusatz in der Abfrage WHERE `Verkaufsdatum` LIKE '2008%' kann ich ja die Abfrage auf ein Jahr beschränken.
Mein Lösungsansatz war: Für jedes Jahr eine eigene Abfrage machen und die Spalten im Excel nebeneinander einfügen. Nun ist dabei aber das Problem dass manche Artikel in einem Jahr nicht verkauft worden sind. Wenn ich jetzt die Abfrageergebnisse in Excel nebeneinander lege und zum Beispiel im Jahr 2009 der Artikel "Schlagmichnich" nicht verkauft worden ist, dann verschieben sich total die Zeilen und es stimmt nichts mehr.

Hat jemand eine Idee wie ich so etwas in eine Abfrage packen könnte?
Vielen Dank.

schöne Grüße
Whirly
Mitglied: Biber
17.05.2011 um 11:27 Uhr
Moin Whirly,

in der Regel löse ich Probleme wie das vorliegende mit einer Hilfstabelle ("Zeitdimensionen"), die in der erfordelichen Granularität die auszuwertenden Zeiträume enthält.
Also eine Struktur mit mindestens den Spalten "Jahr", "Quartal", Monat", in die du von Hand oder per Prozedur lückenlos alle Werte von meinetwegen 2008 bis 2025 einträgst.
Gegen diese Tabelle kannst du deine Tabelle (die ja mindestens ein Datumsfeld enthält) mit einem LEFT JOIN oder einem FULL OUTER JOIN verknibbeln.

Grüße
Biber
Bitte warten ..
Mitglied: KUBLIdata
17.05.2011 um 13:06 Uhr
Da hilft sicher :
GROUP BY year(Artikeldatum),
die summen sind i.o.
Bitte warten ..
Mitglied: Whirly
26.05.2011 um 10:44 Uhr
Hallo Biber,
hallo KUBLIdata,

vielen Dank für eure Antworten.

@ Biber: dein Denkansatz mit der Hilfstabelle ist (glaube ich toll). Jedoch bin ich nicht so fit in Sachen JOIN.
@ KUBLIdata: hab ich kurz ausprobiert. Nur konnte ich das nicht so hinbigen dass ich es gebrauchen könnte.

Ich habe aber die Sache anders gelöst (sagen wir mal so: Sehr unperformant (falls es das Wort gibt))
Ich mache für jeden Artikel und jedes Jahr eine Abfrage. (siehe Code) (nur zur Info)

01.
Sub Artikelumsätze_aktualisieren() 
02.
 
03.
  Sheets("Artikelumsätze").Activate 
04.
  ActiveSheet.Label1.Visible = True   ' "Bitte Warten" einblenden (wird am Ende des Programms wieder ausgeblendet 
05.
     
06.
  Dim zaehler As Integer   'Dieser Zähler wir hochgezählt wenn eine leere Zeile aktualisiert wird. Wenn 4 leere Zeilen hintereinander kommen dann kann man sich sicher sein dass das ende der Liste erreicht worden ist. 
07.
  Dim zeile As Integer 
08.
  Dim Artikelnummer As String 
09.
  Dim SQLstring(20) As String 
10.
   
11.
  zeile = 3 
12.
  zaehler = 0 
13.
  Do 
14.
    Artikelnummer = Range("A" & zeile) 
15.
    If Artikelnummer = "" Then zaehler = zaehler + 1 Else zaehler = 0 
16.
     
17.
  ' Begin mit der ersten Auswertung (Gesamtumsatz,Gesamtmenge) 
18.
    SQLstring(0) = "SELECT SUM(`GP`), SUM(`MENGE`)" 
19.
    SQLstring(1) = "FROM `mand9`.`sg_auf_fpos`, `mand9`.`sg_auf_fschrift`, `mand9`.`sg_auf_artikel`" 
20.
    SQLstring(2) = "WHERE `sg_auf_fpos`.`SG_AUF_FSCHRIFT_1_FK`=`sg_auf_fschrift`.`SG_AUF_FSCHRIFT_PK`" 
21.
    SQLstring(3) = "AND `sg_auf_artikel`.`ARTNR`='" & Artikelnummer & "'" 
22.
    SQLstring(4) = "AND `sg_auf_artikel`.`SG_AUF_ARTIKEL_PK`= `sg_auf_fpos`.`SG_AUF_ARTIKEL_FK`" 
23.
    SQLstring(5) = "AND `ERFART`='04RE'" 
24.
    SQLstring(6) = "AND `sg_auf_fschrift`.`KUNDGR`='Vertreter'" 
25.
    SQLstring(7) = "GROUP BY `sg_auf_fpos`.`SG_AUF_ARTIKEL_FK`" 
26.
    SQLstring(8) = "" 
27.
     
28.
    With ActiveSheet.QueryTables.Add(Connection:="ODBC;DATABASE=mand9;DSN=GS-Abfrage;OPTION=0;PWD=gast;PORT=0;SERVER=server-pu;UID=gast;", Destination:=Cells(zeile, 3)) 
29.
        .CommandText = SQLstring(0) & SQLstring(1) & SQLstring(2) & SQLstring(3) & SQLstring(4) & SQLstring(5) & SQLstring(6) & SQLstring(7) & SQLstring(8) 
30.
        .Name = "" 
31.
        .FieldNames = False 
32.
        .RowNumbers = False 
33.
        .FillAdjacentFormulas = False 
34.
        .PreserveFormatting = True 
35.
        .RefreshOnFileOpen = False 
36.
        .BackgroundQuery = True 
37.
        .RefreshStyle = xlOverwriteCells 
38.
        .SavePassword = True 
39.
        .SaveData = True 
40.
        .AdjustColumnWidth = False 
41.
        .RefreshPeriod = 0 
42.
        .PreserveColumnInfo = True 
43.
        .Refresh BackgroundQuery:=False 
44.
    End With 
45.
    Cells(zeile, 3).NumberFormat = "#,##0.00 $" 
46.
    Cells(zeile, 4).NumberFormat = "0" 
47.
    If Cells(zeile, 3).Value = "" And Artikelnummer <> "" Then Cells(zeile, 3).Value = 0 
48.
    If Cells(zeile, 4).Value = "" And Artikelnummer <> "" Then Cells(zeile, 4).Value = 0 
49.
     
50.
    Dim zaehler2 As Integer 
51.
    Dim Jahr As Integer 
52.
    zaehler2 = 5 
53.
    Jahr = Cells(1, zaehler2).Value 
54.
    Do 
55.
     
56.
      ' Beginn mit der Jahresauswertung (Gesamtumsatz,Gesamtmenge pro Jahr) 
57.
      SQLstring(0) = "SELECT SUM(`GP`), SUM(`MENGE`)" 
58.
      SQLstring(1) = "FROM `mand9`.`sg_auf_fpos`, `mand9`.`sg_auf_fschrift`, `mand9`.`sg_auf_artikel`" 
59.
      SQLstring(2) = "WHERE `sg_auf_fpos`.`SG_AUF_FSCHRIFT_1_FK`=`sg_auf_fschrift`.`SG_AUF_FSCHRIFT_PK`" 
60.
      SQLstring(3) = "AND `sg_auf_artikel`.`ARTNR`='" & Artikelnummer & "'" 
61.
      SQLstring(4) = "AND `sg_auf_artikel`.`SG_AUF_ARTIKEL_PK`= `sg_auf_fpos`.`SG_AUF_ARTIKEL_FK`" 
62.
      SQLstring(5) = "AND `ERFART`='04RE'" 
63.
      SQLstring(6) = "AND `sg_auf_fschrift`.`KUNDGR`='Vertreter'" 
64.
      SQLstring(7) = "AND `sg_auf_fschrift`.`DATUM` LIKE '" & Jahr & "%'" 
65.
      SQLstring(8) = "GROUP BY `sg_auf_fpos`.`SG_AUF_ARTIKEL_FK`" 
66.
     
67.
      With ActiveSheet.QueryTables.Add(Connection:="ODBC;DATABASE=mand9;DSN=GS-Abfrage;OPTION=0;PWD=gast;PORT=0;SERVER=server-pu;UID=gast;", Destination:=Cells(zeile, zaehler2)) 
68.
          .CommandText = SQLstring(0) & SQLstring(1) & SQLstring(2) & SQLstring(3) & SQLstring(4) & SQLstring(5) & SQLstring(6) & SQLstring(7) & SQLstring(8) 
69.
          .Name = "" 
70.
          .FieldNames = False 
71.
          .RowNumbers = False 
72.
          .FillAdjacentFormulas = False 
73.
          .PreserveFormatting = True 
74.
          .RefreshOnFileOpen = False 
75.
          .BackgroundQuery = True 
76.
          .RefreshStyle = xlOverwriteCells 
77.
          .SavePassword = True 
78.
          .SaveData = True 
79.
          .AdjustColumnWidth = False 
80.
          .RefreshPeriod = 0 
81.
          .PreserveColumnInfo = True 
82.
          .Refresh BackgroundQuery:=False 
83.
      End With 
84.
      Cells(zeile, zaehler2).NumberFormat = "#,##0.00 $" 
85.
      Cells(zeile, zaehler2 + 1).NumberFormat = "0" 
86.
      If Cells(zeile, zaehler2).Value = "" And Artikelnummer <> "" Then Cells(zeile, zaehler2).Value = 0 
87.
      If Cells(zeile, zaehler2 + 1).Value = "" And Artikelnummer <> "" Then Cells(zeile, zaehler2 + 1).Value = 0 
88.
     
89.
      zaehler2 = zaehler2 + 2 
90.
      Jahr = Cells(1, zaehler2).Value 
91.
    Loop While Jahr <> 0 
92.
     
93.
    zeile = zeile + 1 
94.
  Loop While zaehler < 4 
95.
  ActiveSheet.Label1.Visible = False     ' "Bitte Warten" wieder ausblenden 
96.
End Sub
liebe Grüße
Whirly

[Edit Biber] Codeformatierung [/Edit]
Bitte warten ..
Mitglied: KUBLIdata
26.05.2011 um 10:57 Uhr
Hallo mal eben

Ich bin hauptberuflicher Datenbankentwickler. Nur werden mir die Fragen und Aufgaben halt etwas anders gestellt.
Ich bin mich gewohnt, dass ich das IST erhalte und das SOLL liefere.
Es ist hier natürlich schwierig, die ganzen Tabellen und Beziehungen abzubilden.
Das "unperformant" (danke für das Wort, finde ich sehr gut formuliert) stimmt natürlich und man könnte sogar böse Worte
wie "Gebastel" dafür verwenden. Nun ja, vor 12 Jahren fing ich auch so an. Das bessert sich laufend mit der Erfahrung.
Auch etwas Literatur (SQL generell) schadet nicht.
Nun ist Ihre Frage damit letztendlich nnicht beantwortet, das weiss ich.
Man hat mich hier schon über meine schweizer Webseite Kublidata . ch kontaktiert, wo ich dann meine Mailadresse
preis gab.
Und sonst: viel Performanz. Ich bin sicher dass Sie das schaffen werden!
Gruss Kudata
Bitte warten ..
Mitglied: Biber
26.05.2011 um 11:54 Uhr
Moin Whirly,

nein, das Wort "unperformant" gibt es nicht - deine Abfrage ist "inperformant"

Was an meiner Skizze ist denn unverständlich?
Leg doch mal eine Spieltabelle "zeitdimensionen" an für diese 3 Jahre, füll es mit Daten und poste die Struktur/das CREATE TABLE.

Dann schauen wir weiter - und das hier im Beitrag.

Grüße
Biber
Bitte warten ..
Neuester Wissensbeitrag
Internet

Unbemerkt - Telekom Netzumschaltung! - BNG - Broadband Network Gateway

(3)

Erfahrungsbericht von ashnod zum Thema Internet ...

Ähnliche Inhalte
PHP
MySQL-Abfrage mit php: Wert + true bzw. false (2)

Frage von tomolpi zum Thema PHP ...

Datenbanken
gelöst MYSQL Abfrage (20)

Frage von datadexx zum Thema Datenbanken ...

Datenbanken
gelöst SQL-Abfrage - DISTINCT - letztes Datum (3)

Frage von emeriks zum Thema Datenbanken ...

Datenbanken
gelöst Row Number bei einer Abfrage sinnvoll einsetzen (1)

Frage von Aximand zum Thema Datenbanken ...

Heiß diskutierte Inhalte
Windows Server
Outlook Verbindungsversuch mit Exchange (15)

Frage von xbast1x zum Thema Windows Server ...

Microsoft Office
Keine Updates für Office 2016 (12)

Frage von Motte990 zum Thema Microsoft Office ...

Grafikkarten & Monitore
Tonprobleme bei Fernseher mit angeschlossenem Laptop über HDMI (11)

Frage von Y3shix zum Thema Grafikkarten & Monitore ...