Top-Themen

Aktuelle Themen (A bis Z)

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 ?

Mitglied: Whirly

Whirly (Level 1) - Jetzt verbinden

17.05.2011 um 10:46 Uhr, 4127 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 ..
Ähnliche Inhalte
Datenbanken
MYSQL Abfrage
gelöst Frage von datadexxDatenbanken20 Kommentare

Halo an alle MySQL Spezialisten! Wie kann ich denn am besten folgende Werte abfragen und berechnen? Ausgang für die ...

PHP
PHP MySQL Abfrage aus Datenbank
Frage von Lost144PHP12 Kommentare

Hallo, Ich habe in einer Datenbank daten über VM's stehen. Angaben wie hostname,adresse, serverat usw. Wie kann ich in ...

Datenbanken
MYSQL Abfrage bereitet mir Probleme
gelöst Frage von datadexxDatenbanken43 Kommentare

Hallo Leute, ich habe wieder ein Problem mit einer SQL Abfrage. Ich habe eine Tabelle die nennt sich pbooking, ...

Datenbanken
(Prozent)s in einer MySQL-Abfrage
gelöst Frage von itebobDatenbanken4 Kommentare

Was bedeutet where nr=%s in einer MySQL-Abfrage? Sowas wie %s gehört bestimmt zu den MySQL-Grundlagen. In meinem Tutorial aber ...

Neue Wissensbeiträge
Sicherheit

MikroTik-Router patchen, Schwachstelle wird ausgenutzt

Information von kgborn vor 2 StundenSicherheit

Am 23. April 2018 wurde von Mikrotik ein Security Advisory herausgegeben, welches auf eine Schwachstelle im RouterOS hinwies. Mikrotik ...

Windows 10

Microcode-Updates KB4090007, KB4091663, KB4091664, KB4091666 für Windows 10

Information von kgborn vor 8 StundenWindows 101 Kommentar

Kurze Information für Administratoren von Windows 10-Systemen, die mit neueren Intel CPUs laufen. Microsoft hat zum 23. April 2018 ...

iOS
Updates für Iphone und Co
Information von sabines vor 12 StundeniOS

Gestern abend ist iOS 11.3.1 erschienen, ein kleineres Update, dass einige Lücken schließt und "Lahmlegen" nach einem Display Tausch ...

Windows 7

Windows 7 - Server 2008 R2: Exploit für Total Meltdown verfügbar

Information von kgborn vor 1 TagWindows 7

Kleine Information für Administratoren, die für die Updates von Windows 7 SP1 und Windows Server 2008 R2 SP1 verantwortlich ...

Heiß diskutierte Inhalte
Batch & Shell
Powershell: Im AD nach Rechnern mit bestimmten IP-Adressen suchen
gelöst Frage von Raven42Batch & Shell36 Kommentare

Hallo zusammen, ich suche nach einer Möglichkeit nach Computern im AD zu suchen , deren IP-Adresse mit 10.11.12. beginnt. ...

C und C++
Frage1 C Programmierung-Makefile Frage2 PHP-Programmierung HTTP-Fehler 404
Frage von KatalinaC und C++34 Kommentare

Hallo, ich habe 2 Fragen, die nichts miteinander zu tun haben aber mit denen ich mich gerade beschäftige: 1. ...

LAN, WAN, Wireless
Watchguard T15 VPN Einrichtung
gelöst Frage von thomasjayLAN, WAN, Wireless25 Kommentare

Hallo zusammen, wir möchten gerne über unsere Watchguard T15 einen VPN-Tunnel (Mobile VPN with IPSec) einrichten! Als Client nutzen ...

Windows Server
Alten DC entfernen
Frage von smartinoWindows Server24 Kommentare

Hallo zusammen, ich habe hier eine Umgebung übernommen und erstmal einen DCDIAG gemacht. Dabei fällt auf, daß eine ganze ...