datadexx
Goto Top

MYSQL Abfrage

Halo an alle MySQL Spezialisten!

Wie kann ich denn am besten folgende Werte abfragen und berechnen?

Ausgang für die ersten Infos die möchte ist die Tabelle "posten" in der die Werte netto, brutto, kosten, datum, kd_id enthalten sind.

Was ich erreichen möchte ist folgendes Ergebnis immer fortlaufend für das ganze Jahr:

Umsatzstärkster Tag = z.B: am 08.04.2016 mit 23.456,00 € netto Umsatz

Höchstes Ergebnis = z.B. am 23.11.2016 mit 10.500,00 €

Höchste Rendite = z.B. am 03.03.2016 mit 35,04 %

Außerdem möchte ich mir gerne für jeden Monat die 15 umsatzstärksten Kunden auf Basis der oben genannten "posten" Tabelle und der Tabelle "kunden" wie folgt anzeigen lassen:

Die Tabellen "posten" und "kunden" sind verknüpft: Tabelle "kunden" Feld id, Tabelle "posten" Feld kd_id.

Januar:
1. Kunde 1 = 123.000,00 €
2. Kunde 2 = 98.560,00. €
...
...
...

Februar:
1. Kunde 1 = 212.000,00 €
2. Kunde 2 = 148.560,00. €

Wer von euch kann das aus dem FF face-smile und mag mir helfen?

THX!!!

Content-Key: 304320

Url: https://administrator.de/contentid/304320

Ausgedruckt am: 19.03.2024 um 07:03 Uhr

Mitglied: BirdyB
BirdyB 12.05.2016 um 13:47:34 Uhr
Goto Top
Hi,
kurze Nachfrage:
Zitat von @datadexx:

Halo an alle MySQL Spezialisten!

Wie kann ich denn am besten folgende Werte abfragen und berechnen?

Ausgang für die ersten Infos die möchte ist die Tabelle "posten" in der die Werte netto, brutto, kosten, datum, kd_id enthalten sind.

Was ich erreichen möchte ist folgendes Ergebnis immer fortlaufend für das ganze Jahr:

Umsatzstärkster Tag = z.B: am 08.04.2016 mit 23.456,00 € netto Umsatz
Der Umsatz ist dann die Summer der einzelnen Posten des Tages?
Höchstes Ergebnis = z.B. am 23.11.2016 mit 10.500,00 €
Meinst du damit den höchsten Einzelposten?
Höchste Rendite = z.B. am 03.03.2016 mit 35,04 %
Die Rendite bezieht sich auf brutto oder auf netto? (vermutlich die blödeste Frage von mir)
Außerdem möchte ich mir gerne für jeden Monat die 15 umsatzstärksten Kunden auf Basis der oben genannten "posten" Tabelle und der Tabelle "kunden" wie folgt anzeigen lassen:

Die Tabellen "posten" und "kunden" sind verknüpft: Tabelle "kunden" Feld id, Tabelle "posten" Feld kd_id.

Januar:
1. Kunde 1 = 123.000,00 €
2. Kunde 2 = 98.560,00. €
...
...
...

Februar:
1. Kunde 1 = 212.000,00 €
2. Kunde 2 = 148.560,00. €

Wer von euch kann das aus dem FF face-smile und mag mir helfen?
Ich denke, dein Problem ist lösbar face-wink
THX!!!

Falls du es selbst probieren möchtest, hier schonmal ein paar Anmerkungen:
Zu Punkt 1: Du musst in der Abfrage die Ergebnisse tagesbasiert gruppieren also die Datensätze Abfragen und mittels GROUP BY nach Datum gruppieren und dann mittels sum die Summe wählen.

Zu Punkt 2: Hier kannst du mit der Funktion MAX das Maximum auswählen.

Zu Punkt 3: Die Rendite kannst du ja auch im select berechnen lassen und das Ergebnis nach den höchsten Werten sortieren.

Zu Punkt 4: Hier musst du einfach die Kundentabelle an die Abfrage joinen. Wenn du die Ergebnisse nach der Kunden-ID gruppierst kannst du die Summe pro Kunde abfragen und absteigend sortieren.

Wenn du nicht weiterkommst, dann sag Bescheid, dann verpacke ich das auch in SQL-Code face-wink

Beste Grüße!


Berthold
Mitglied: datadexx
datadexx 12.05.2016 um 15:09:19 Uhr
Goto Top
Hi Berthold!
Vorab schon mal Danke an Dich! Ich versuche noch ein wenig mehr zu erklären, denn leider komm ich allein nicht wirklich weiter face-smile hätte ja sonst auch nicht hier nachgefragt face-smile.

Zu deiner ersten Frage: Um an das Ergebnis was ich haben will zu kommen, so denke ich mir das, müsste die Abfrage im Zeitraum 01.01.2016 - 31.12.2016 die Summe aller Posten für jeden Tag errechnen und mir dann den Tag mit der höchsten Summe ausgeben. Theoretisch müsste sich der Tag, falls an einem neuen Tag mehr Umsatz gemacht wurde, dann mit fortschreiten des Jahres ja auch ändern...

Zu deiner zweiten Frage: nein nicht den höchsten Einzelposten, sondern das höchste Tagesergebnis des Jahres (netto Summe des Tages - Summe kosten des tages). auch hier kann sich beim fortschreiten des Jahres auch der tag wieder ändern, wenn an einem Tag ein besseres Ergebnis erzielt wurde...

Zu deiner dritten Frage der Rendite : auf netto face-smile

Richtig Probleme macht mir auch nur das obere SQL...

Die 15 umsatzstärksten Kunden hab ich bereits angefangen, hier mein SQL, aber ich will das die Abfrage mir direkt immer die 15 besten für alle Monate ausgibt, also direkt Januar bis Dezember.

SELECT k.firma AS Firma, MONTH(p.datum) AS Monat, SUM(p.gesamt) AS Umsatz FROM posten p JOIN kunden k ON k.id = p.kd_id WHERE YEAR(p.datum) = '2016' GROUP BY k.firma ORDER BY Monat,Umsatz DESC LIMIT 0,15

Das SQL gibt mir aber immer nur Januar aus, nicht Januar bis Dezember

THX!!!
Mitglied: datadexx
datadexx 12.05.2016 um 15:12:32 Uhr
Goto Top
Ich denke mir bei meinem SQL für die 15 besten, das man da mit einer Subquery arbeiten muss? Also eigentlich jeden Monat durchlaufen und mit der Subquery dann die 15 besten ermitteln, aber da hörts bei mir auf face-smile
Mitglied: BirdyB
Lösung BirdyB 12.05.2016 aktualisiert um 15:37:51 Uhr
Goto Top
Mal so aus dem Bauch heraus und ungetestet: EDIT: Jetzt auch getestet...
SELECT
datum,
sum(netto) as tagessumme
FROM posten
WHERE YEAR(datum) = '2016'  
GROUP BY datum
ORDER BY tagessumme DESC
LIMIT 0,1
Das sollte dir den umsatzstärksten Tag des laufenden Jahres ausgeben.
Für das beste Tagesergebnis:
SELECT 
datum, 
sum(netto) - sum(kosten) as tagesergebnis 
FROM posten 
GROUP BY datum 
ORDER BY tagesergebnis DESC 
LIMIT 0,1
Für die Tagesrendite:
SELECT datum, 
(sum(kosten) / sum(netto)) as tagesrendite 
FROM posten 
GROUP BY datum 
ORDER BY tagesrendite DESC 
LIMIT 0,1
Mitglied: BirdyB
BirdyB 12.05.2016 um 15:37:07 Uhr
Goto Top
Zitat von @datadexx:

Ich denke mir bei meinem SQL für die 15 besten, das man da mit einer Subquery arbeiten muss? Also eigentlich jeden Monat durchlaufen und mit der Subquery dann die 15 besten ermitteln, aber da hörts bei mir auf face-smile

Subquerys sind auch nicht so schwer. Baue deine bisherige Abfrage einfach mal für einen Monat um und dann machst du einfach folgendes:
SELECT
(SELECT .. Abfrage für Januar)
(SELECT ...Abfrage für Februar)
usw.
Mitglied: datadexx
datadexx 12.05.2016 um 15:39:18 Uhr
Goto Top
VIELEN DANK erstmal Berthold!!! Ich werde das mal testen mit den Subquerys und entweder vom Erfolg oder Mißerfolg berichten face-smile !!!
Mitglied: BirdyB
BirdyB 12.05.2016 um 15:42:11 Uhr
Goto Top
Immer gerne... Wenn es funktioniert, dann bitte noch den Beitrag als gelöst markieren und die zugehörigen Lösungsbeiträge auch...
Wenn du noch Hilfe bei der letzten Problemstellung benötigst, sag Bescheid.
Mitglied: datadexx
datadexx 12.05.2016 um 16:24:04 Uhr
Goto Top
Hey Berthold, ich hab hier eine kleine Korrektur der Berechnung durchgeführt, da das Ergebnis nicht stimmen konnte. Schau mal bitte:

((sum(netto) - sum(kosten))*100) / sum(netto) AS tagesrendite

Da komm ich auf einen Wert der passen kann face-smile
Mitglied: BirdyB
BirdyB 12.05.2016 um 16:25:23 Uhr
Goto Top
Wenn du das Ergebnis in Prozent haben willst, dann ist deine Anpassung richtig... Ich hatte es einfach dezimal ausgerechnet...
Mitglied: datadexx
datadexx 12.05.2016 um 16:39:44 Uhr
Goto Top
Ahhh ok, perfekt. Sag später über den restlichen "Erfolg" Bescheid.

THX!
Mitglied: datadexx
datadexx 13.05.2016 um 13:34:10 Uhr
Goto Top
Berthold ich brauch dich noch mal face-smile !

Sry erstmal, ich konnte gestern nicht mehr dran weiter machen.

Ich hab meine Abfrage jetzt umgebaut auf einen Monat, aber ich versteh das mit dem Aufbau der Subquery leider nicht.

Wenn ich

SELECT
(SELECT bla bla),
(SELCT bla bla),

usw. mache gibts ne Fehlermeldung. Wie muss denn das äußere SELECT aussehen?
Mitglied: BirdyB
BirdyB 13.05.2016 um 13:57:35 Uhr
Goto Top
Stimmt, war mein Fehler...
In diesem Fall darf der Select nur einen Wert zurückgeben...

Ich habs jetzt mal ohne JOIN der Kundendaten gemacht (Angelehnt an deine Abfrage von oben):
SELECT kunden_id,MONTH(p.datum) AS Monat, SUM(p.netto) AS Umsatz FROM posten p WHERE YEAR(p.datum) = '2016' GROUP BY kunden_id,Monat ORDER BY Monat,Umsatz DESC LIMIT 0,15;  
Das ergibt mit meinen Beispieldaten dann in etwa sowas:
+-----------+-------+--------+
| kunden_id | Monat | Umsatz |
+-----------+-------+--------+
|         1 |     4 |   1579 |
|         2 |     4 |   1518 |
|         1 |     5 |    130 |
|         2 |     5 |     90 |
+-----------+-------+--------+
Damit hättest du nachfolgend für jeden Monat die Top15 hintereinander aufgelistet (Ich hab nur 4 Einträge in der Tabelle).

Wenn du Zwischenüberschriften brauchst, müsstest du mit mehreren Abfragen arbeiten.
Mitglied: Biber
Biber 13.05.2016 um 13:59:33 Uhr
Goto Top
Moin datadexx,

sinngemäß ungefähr so:
SELECT alles.* from (

SELECT Month(datumsfeld) as Monat, Kunde,  sum(feld2) as SummeX, Avg( feld3) as Durchschnitt  ...
FROM posten WHERE year(datumsfeld) = 2016 and month(datumsfeld)  = 1 
GROUP BY Month(datumsfeld), Kunde
ORDER BY 3 desc
Limit 15

UNION ALL

SELECT Month(datumsfeld) as Monat, Kunde,  sum(feld2) as SummeX, Avg( feld3) as Durchschnitt  ...
FROM posten WHERE year(datumsfeld) = 2016 and month(datumsfeld) = 2
GROUP BY Month(datumsfeld), Kunde
ORDER BY 3 desc
Limit 15

UNION ALL
....

UNION ALL
SELECT Month(datumsfeld) as Monat, Kunde,  sum(feld2) as SummeX, Avg( feld3) as Durchschnitt  ...
FROM posten WHERE year(datumsfeld) = 2016 and month(datumsfeld) = 12
GROUP BY Month(datumsfeld), Kunde
ORDER BY 3 desc
Limit 15

 ) alles

Grüße
Biber
Mitglied: datadexx
datadexx 13.05.2016 um 14:03:28 Uhr
Goto Top
Hey THX!

Zwischenüberschriften bräuchte ich nicht unbedingt, aber interessieren würde es mich. auch wie ich es anstelle, das nicht die Monatszahl sondern der Name ausgegebn wird. Was ich wohl brauche ist den Firmennamen, da ich die Kundennummern nicht im Kopf habe, so weiß ich nicht welcher Kunde das ist und müsste immer nachschauen... aber evtl. kann ich mir da in PHP was bauen...
Mitglied: Biber
Biber 13.05.2016 aktualisiert um 14:19:50 Uhr
Goto Top
Moin datadexx,

bezogen auf mein Beispiel kannst du ja durchaus an die Alles-Ergebnis-Tabelle einen Join über die Kundennr auf deine Kunden machen.
Watt willst du denn da in PHP rumrödeln?

SELECT Alles.Monat, Kunden.Kundenname, Alles.SummeX, Alles.Durchschnitt ...
FROM (

<siehe oben>
) Alles
join Kunden on Alles.kunde = Kunden.kundennummer

Grüße
Biber
Mitglied: datadexx
datadexx 13.05.2016 um 14:51:51 Uhr
Goto Top
Danke Biber!
Werde das mal testen.

Berthold seine Abfrage gibt mit dem Limit 0,15 natürlich nur den Januar aus, das müsste auch mit einem äußeren SELECT gemacht werden oder sehe ich das jetzt falsch?

SELECT kunden_id,MONTH(p.datum) AS Monat, SUM(p.netto) AS Umsatz FROM posten p WHERE YEAR(p.datum) = '2016' GROUP BY kunden_id,Monat ORDER BY Monat,Umsatz DESC LIMIT 0,15;
Mitglied: datadexx
datadexx 13.05.2016 um 15:45:08 Uhr
Goto Top
Hey Biber,

bei Deinem Beispiel bekomme ich einen UNION ALL und ORDER BY FEHLER...

#1221 - Incorrect usage of UNION and ORDER BY

THX!
Mitglied: Biber
Lösung Biber 14.05.2016 aktualisiert um 16:07:35 Uhr
Goto Top
Moin datadexx,

das ist - wie nachgelesen habe, als ich eine handelsübliche Suchmaschine mit "#1221 - Incorrect usage of UNION and ORDER BY" durch die NSA-Rechner gejagt habe, eher ein Implementierungsproblem der mySQL-Zusammenbräter.

Wobei die sogar schreiben, es wäre kein Bug, weil... es gibt gar keinen realen Businesscase für ein UNION ALL mit inneren ORDER BYs.

Anyhow, funktionieren soll es, wenn die einzelnen 12 Monats-SELECTs, die bei mir jeweils mit UNION ALL getrennt sind, zusätzlich mit normalen Klammern abgegrenzt sind.

Beispiel, ein bisschen angepasst an deine Feldnamen :
SELECT Alles.Monat, Kunden.Kundenname, Alles.Umsatz FROM (

( SELECT 'Jan'  as Monat, Kunden_id,  sum(netto) as Umsatz  
FROM posten WHERE year(datum) = 2016 and month(datum)  = 1 
GROUP BY Kunden_id
ORDER BY 3 desc
Limit 15
)
UNION ALL
(
SELECT 'Feb'  as Monat, Kunden_id,  sum(netto) as Umsatz  
FROM posten WHERE year(datum) = 2016 and month(datum) = 2
GROUP BY Kunden_id
ORDER BY 3 desc
Limit 15
) 

UNION ALL 
( SELECT ...März nach Schema F)
...
UNION ALL 
( SELECT ...Dezember nach Schema F )

) Alles
join Kunden on Alles.kunden_id = Kunden.kunden_id

Ungetestet und soll hauptsächlich das Prinzip zeigen..

Grüße
Biber
Mitglied: Biber
Biber 18.05.2016 um 12:37:32 Uhr
Goto Top
Moin datadexx,

bist du denn weitergekommen oder steckst du noch fest mit deiner Abfrage?

BirdyB steht ja auch noch mit seiner Alternative in den Startlöchern...

Grüße
Biber
Mitglied: datadexx
datadexx 18.05.2016 um 14:41:12 Uhr
Goto Top
Hey Biber und BirdyB,

vielen vielen Dank an euch beide, hab mit eurer Hilfe die Daten abfragen können die ich brauche!
Markiere jetzt die Beiträge noch die zur Lösung beigertragen haben!

Greetz