Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

Suche Hilfe, Select über mehrer Tabellen mit Join und SUM * und +

Dieses Thema im Forum "MySQL und MariaDB" wurde erstellt von Steini, 12 April 2013.

  1. Steini

    Steini Benutzer

    Hallo zusammen,

    ich habe ein mittel- bis großes Problem und hoffe hier die nötige Hilfe zu bekommen.

    Also, ich versuche es mal zu beschreiben:

    Tabelle [Felder]
    vorgang [id, rech_cat, rech_date, rech_nr, kunden_id, zahlungs_eingang]
    positionen [id, rech_nr, artikel_id, menge, pos_zusatz_id, pos_auftrag, sort, artikel_frei_id]
    kunden [id, kunden_id, firma_name, anrede, vorname, nachname, plz, ort, strasse....]
    artikel [id, text, beschreibung, einheit, preis]
    artikel_frei [id, rech_nr, artikel_frei_id, text, beschreibung, einheit, preis]

    Ich versuch jetzt folgende Ausgabe zu bekommen:
    Datum, RechnungsNr, KundenName, FimenName, Summe, Buchungsdatum

    wobei das Problem meiner Meinung nach bei der Summe besteht.
    In Tabelle "vorgang" und "positionen" steht die Rechnungs-Nr.
    In der Tabelle "positionen" stehen die einzelnen Positionen zur jeweiligen Rechnung
    mit Mengenangaben und die dazugehörige Artikel_ID oder Artikel_frei_ID aus den Tabellen
    "Artikel" und "Artikel_frei".

    Jede einzelne Mengenangabe aus Tabelle "positionen" muß mit dem Preis aus der Tabelle
    "artikel" und/oder "artikel_frei" multipliziert werden so das eine Gesamtsumme pro Vorgang
    ausgegeben wird, wäre etwa so etwas
    SUM(P.menge * A.preis) + IFNULL(SUM(P.menge * F.preis), 0)

    Ich habe irgendwie schon alle Variationen von JOIN , UNION und was weiß ich noch alles durch.
    Ich komme einfach nicht weiter.

    bei folgendem Beispiel bekomme ich aber leider nur einen Vorgang ausgegeben:

    "SELECT V.*, K.nachname, K.vorname, K.firma_name,
    SUM(P.menge * A.preis) + IFNULL(SUM(P.menge * F.preis), 0) AS PREIS
    FROM vorgang AS V, kunden AS K, positionen as P
    LEFT JOIN artikel AS A ON P.artikel_id = A.id
    LEFT JOIN artikel_frei AS F ON P.artikel_frei_id = F.artikel_frei_id
    WHERE V.kunden_id = K.kunden_id
    AND V.rech_nr = P.rech_nr
    AND V.rech_cat = 'RE'"


    Wäre prima wenn mir da jemand auf die Sprünge helfen könnte.

    Achso, falls es wichtig ist, ich möchte das per PHP Ausgabe.

    Vielen Dank schonmal, hoffe ist einigermaßen verständlich
     
  2. akretschmer

    akretschmer Datenbank-Guru


    Mal mittel- bis stark vereinfacht:

    Code:
    test=# select * from vorgang;                 
     id                                           
    ----                                          
      1                                           
      2                                           
    (2 rows)                                      
    
    Time: 0,175 ms
    test=*# select * from artikel;
     id | preis                   
    ----+-------                  
      1 |    10                   
      2 |    20                   
      3 |    30                   
    (3 rows)                      
    
    Time: 0,178 ms
    test=*# select * from artikel_frei;
     id | preis                        
    ----+-------                       
      1 |    15                        
      2 |    25                        
      3 |    35                        
    (3 rows)                           
    
    Time: 0,186 ms
    test=*# select * from position;
     id | rechnung | artikel | artikel_frei
    ----+----------+---------+--------------
      1 |        1 |       1 |
      2 |        1 |         |            2
      3 |        2 |       2 |            3
    (3 rows)
    
    das als Ausgang.

    Als Zwischenschritt:
    Code:
    test=*# select p.rechnung, a1.preis, a2.preis from position p left join artikel a1 on p.artikel=a1.id left join artikel_frei a2 on p.artikel_frei=a2.id;
     rechnung | preis | preis
    ----------+-------+-------
            1 |    10 |
            1 |       |    25
            2 |    20 |    35
    (3 rows)
    
    Final:
    Code:
    test=*# select rechnung, sum(coalesce(preis,0))+sum(coalesce(preis_frei)) from (select p.rechnung, a1.preis, a2.preis as preis_frei from position p left join artikel a1 on p.artikel=a1.id left join artikel_frei a2 on p.artikel_frei=a2.id) foo group by 1;
     rechnung | ?column?
    ----------+----------
            1 |       35
            2 |       55
    (2 rows)
    
    Den Rest per JOIN dranzumontieren schaffst Du ;-)

    Du hast in Deinem Versuch schon mal Fehler, auch wenn MySQL das wohl verzeiht: Alle Spalten des Resultats, welche nicht Bestandteil einer Aggregation sind, gehören in das GROUP BY. Das ist SQL-Norm und ALLE Datenbanken erzwingen das, außer MySQL. MySQL errät hier irgendwas, manchmal das, was der Benutzer will. Aber nicht immer.
     
    Steini gefällt das.
  3. Steini

    Steini Benutzer

    ich werd verrückt, und ich brech mir hier einen ab.....
    VIELEN VIELEN DANK @ akretschmer

    ich hab es jetzt genau so wie ich es haben wollte:
    Code:
    select V.*, K.nachname,K.firma_name, P.rech_nr, SUM(coalesce(A1.preis,0) * P.menge) + SUM(coalesce(A2.preis,0) * P.menge) AS PREIS
    FROM positionen P
    LEFT JOIN vorgang V ON P.rech_nr=V.rech_nr
    LEFT JOIN kunden K ON V.kunden_id=K.kunden_id
    LEFT JOIN artikel A1 ON P.artikel_id=A1.id
    LEFT JOIN artikel_frei A2 ON P.artikel_frei_id=A2.artikel_frei_id
    GROUP BY P.rech_nr
    bei der Gelegenheit habe ich mir auch noch einmal die "coalesce" Funktion genauer angeschaut (auch sehr nützlich)

    (ich hoffe in der Abfrage sind keine weiteren Fehler und ich die gewünschte Ausgabe nur zufällig erhalte)

    LG Steini
     
  4. akretschmer

    akretschmer Datenbank-Guru

    Doch, sind Fehler.
    • kein select * in produktivem Code verwenden
    • alle Spalten, die nicht in einer Aggregatfunktion sind, gehören in das GROUP BY

    Änderst Du später mal Deine Tabelle Vorgang (v) stimmt Dein Select hier nicht mehr, in verbindung mit dem, ich muß schon sagen Bug, daß MySQL es nicht erzwingt, daß alle Spalten im GROUP BY sein müssen, wird das Resultat Deiner Abfrage (auch jetzt schon) eher zufällig sein.

    Andreas
     
    Steini gefällt das.
  5. Steini

    Steini Benutzer

    OK, hab ich jetzt angepasst

    Code:
    select
    V.id, V.rech_cat, V.rech_date, V.rech_nr, V.zahlungs_eingang, V.versendet,
    K.nachname, K.vorname, K.kontakt_name, K.kontakt_vorname, K.firma_name, K.kunden_id,
    SUM(coalesce(A1.preis,0) * P.menge) + SUM(coalesce(A2.preis,0) * P.menge) AS PREIS
    FROM positionen P
    LEFT JOIN vorgang V ON P.rech_nr=V.rech_nr
    LEFT JOIN kunden K ON V.kunden_id=K.kunden_id
    LEFT JOIN artikel A1 ON P.artikel_id=A1.id
    LEFT JOIN artikel_frei A2 ON P.artikel_frei_id=A2.artikel_frei_id
    WHERE rech_cat = 'RE'
    GROUP BY V.rech_cat, V.rech_date, V.rech_nr, V.zahlungs_eingang, V.versendet,
    K.nachname, K.vorname, K.kontakt_name, K.kontakt_vorname, K.firma_name, K.kunden_id
    wenn obiger Code keine Fehler mehr enthält habe ich aber jetzt doch noch ein Problem mit dem "ORDER BY"
    vieleicht sehe ich ja auch den Wald vor lauter Bäume nicht mehr.
     
  6. Steini

    Steini Benutzer

    ach QUATSCH, ich darf den ORDER BY ja auch nicht VOR dem GROUP BY packen.....
    jetzt passt es
     
  7. akretschmer

    akretschmer Datenbank-Guru

    Mit dem ORDER BY sehe ich kein Problem, da ich kein ORDER BY sehe.

    Andreas
     
    Steini gefällt das.
  8. Steini

    Steini Benutzer

    mh, zu früh gefreut...

    eine Rechnung "V.rech_nr" ist in Tabelle "VORGANG" angelegt aber es wurde noch keine Position angelegt, demnach ist in der Tabelle "POSITIONEN" noch kein Datensatz "P.rech_nr" vorhanden.
    Der Vorgang fehlt dann natürlich bei der Ausgabe. Sonst sieht die Ausgabe schon perfekt aus.
    Code:
    select
    V.id, V.rech_cat, V.rech_date, V.rech_nr, V.zahlungs_eingang, V.versendet,
    K.nachname, K.vorname, K.kontakt_name, K.kontakt_vorname, K.firma_name, K.kunden_id,
    SUM(coalesce(A1.preis,0) * coalesce(P.menge,0)) + SUM(coalesce(A2.preis,0) * coalesce(P.menge,0)) AS PREIS
    FROM positionen P
    LEFT JOIN vorgang V ON P.rech_nr = V.rech_nr
    LEFT JOIN kunden K ON V.kunden_id = K.kunden_id
    LEFT JOIN artikel A1 ON P.artikel_id = A1.id
    LEFT JOIN artikel_frei A2 ON P.artikel_frei_id=A2.artikel_frei_id
    GROUP BY V.rech_cat, V.rech_date, V.rech_nr, V.zahlungs_eingang, V.versendet, K.nachname, K.vorname, K.kontakt_name, K.kontakt_vorname, K.firma_name, K.kunden_id
    LEFT JOIN vorgang V ON P.rech_nr = V.rech_nr


    Macht das jetzt Sinn einen leeren Datensatz in der Tabelle "POSITIONEN" anzulegen oder
    kann man das in der Abrage noch irgendwie abfangen ?
     
  9. akretschmer

    akretschmer Datenbank-Guru

    Nein. Du drehst die JOIN-Reihenfolge. Zuerst Vorgang, dann Position. Einklich ganz logisch, gell?
     
    Steini gefällt das.
  10. Steini

    Steini Benutzer

    oh man, das Leben könnte soooo einfach sein wenn man öfter mal die Augen auf macht....

    hier jetzt meine fertige Abfrage, scheint so das alles jetzt funktioniert:

    Code:
    select
    V.id, V.rech_cat, V.rech_date, V.rech_nr, V.zahlungs_eingang, V.versendet,
    K.nachname, K.vorname, K.kontakt_name, K.kontakt_vorname, K.firma_name, K.kunden_id,
    SUM(coalesce(A1.preis,0) * coalesce(P.menge,0)) + SUM(coalesce(A2.preis,0) * coalesce(P.menge,0)) AS PREIS
    FROM vorgang V
    LEFT JOIN positionen P ON P.rech_nr = V.rech_nr
    LEFT JOIN kunden K ON V.kunden_id = K.kunden_id
    LEFT JOIN artikel A1 ON P.artikel_id = A1.id
    LEFT JOIN artikel_frei A2 ON P.artikel_frei_id = A2.artikel_frei_id
    GROUP BY V.rech_nr, V.rech_cat, V.rech_date, V.zahlungs_eingang, V.versendet, K.nachname, K.vorname, K.kontakt_name, K.kontakt_vorname, K.firma_name, K.kunden_id
    ich habe da zwar noch dynamische WHERE und ORDER BY Klauseln drin die per PHP generiert werden aber das ist ja nur Spielerei am Rande.

    Funktionirt SUPER, VIELEN DANK für die schnelle und vor allem professionelle Hilfe..
     
  11. akretschmer

    akretschmer Datenbank-Guru

    Na, da könnte man ja auch mal auf Gefällt mir klicken, oder? ;-)
     
    Steini gefällt das.
  12. Steini

    Steini Benutzer

    das ist absolut richtig... ach da ich der link.. da drück ich jetzt aber auch gerne mal druf. ;)
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden