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

Summe einer TeilAbfrage

Dieses Thema im Forum "Microsoft SQL Server" wurde erstellt von Hazel4tw1, 4 Februar 2014.

  1. Hazel4tw1

    Hazel4tw1 Aktiver Benutzer

    Hallo zusammen,

    warscheinlich ist das eine ganz einefache Sache aber ich kriege es nicht hin.

    Und zwar versuch ich eine auflistung zu machen die wie folgt aussehen soll.

    Es sollen die 10 größten Werte einer Tabelle gezogen werden und die restlichen Werte sollen zusammen gefasst als 11 zeile ausgeben werden.

    Es hackt bei diesem elften wert. Dieser elfte wert könnte auch über eine eigene Abfrage ausgegeben werden, schöner wäre es natürlich in einem.

    Hier meine Versuche(muss nicht angekuckt werden, ist eh nur quatsch)
     
  2. akretschmer

    akretschmer Datenbank-Guru


    Vielleicht so:

    Code:
    test=# create table hazel as select s, (random() * 10)::int as value from generate_series(1,10) s;
    SELECT 10
    test=*# select * from hazel ;
     s  | value
    ----+-------
      1 |  9
      2 |  1
      3 |  0
      4 |  2
      5 |  2
      6 |  10
      7 |  5
      8 |  4
      9 |  2
     10 |  6
    (10 rows)
    
    test=*# create view view_hazel as select s, value, row_number() over (order by value desc) from hazel ;
    CREATE VIEW
    test=*# select * from view_hazel where row_number <= 5 union select null, sum(value), null from view_hazel where row_number > 5 order by row_number nulls last;
     s  | value | row_number
    ----+-------+------------
      6 |  10 |  1
      1 |  9 |  2
     10 |  6 |  3
      7 |  5 |  4
      8 |  4 |  5
      |  7 |
    (6 rows)
    
    PostgreSQL, könnte in M$SQL aber auch ähnlich gehen.
     
  3. Hazel4tw1

    Hazel4tw1 Aktiver Benutzer

    Hey,

    danke für die schnelle Antwort.

    Leider habe ich nicht die möglichkeit create oder ähnliche befehle auszuführen.

    Es funktionieren nur Select Befehle, zudem werden die Daten später an eine Report Engine weiter verarbeitet.
     
  4. akretschmer

    akretschmer Datenbank-Guru

    Das create hab ich doch nur gebraucht, um Beispieldaten zu erstellen. Denk Dir das einfach weg...
     
  5. ukulele

    ukulele Datenbank-Guru

    Es gibt vermutlich viele Wege aber mein Favorit wäre soetwas hier:
    Code:
    SELECT    zeilennr,
            count(*)
    FROM    (
    
    SELECT    (    CASE
                WHEN    ROW_NUMBER() OVER (ORDER BY spalte) <= 10
                THEN    ROW_NUMBER() OVER (ORDER BY spalte)
                ELSE    11
                END ) AS zeilennr,
            spalte
    FROM    tabelle
    
    ) t
    GROUP BY zeilennr
    Ich glaube der Select muss geschachtelt werden um nach ROW_NUMBER() gruppieren zu können. Falls das in deiner Anwendung nicht zulässig ist müsstest du mal UNION probieren.
     
  6. Hony%

    Hony% Datenbank-Guru

    Hi.

    Code:
    SELECT value
      FROM (
        SELECT value
        FROM test
        ORDER BY value DESC
        LIMIT 10
      ) AS T
    UNION ALL
      SELECT SUM(A.value)
        FROM test AS A
        LEFT JOIN
        (
          SELECT value
            FROM test
            ORDER BY value DESC
            LIMIT 10
        ) AS B
        ON A.value = B.value
        WHERE B.value IS NULL
    
    So sollte das auch unter MySQL funktionieren. Das verschachtelte Select ist notwendig, da MySQL sonst wegen ORDER BY mault.
     

    Anhänge:

  7. Hazel4tw1

    Hazel4tw1 Aktiver Benutzer

    Hi,

    irgendwie ist mir das zu hoch :S
    Wurde irgendwie in das SQL becken quasi nackt geworfen.

    Könnt ihr mir anhand meiner Spalten namen ein Beispiel zeigen?

    Folgendes habe ich:

    Engineer_Displayname -> Name zum Bezug Stunden
    TimeEntry_WorkingTime_Total -> Werte wo ich die höchsten 10 brauche und als 11 die summe der restlichen

    Das ganze muss gruppiert nach Engineer_Displayname sein, da es auch mehrere Stunden unter TimeEntry_WorkingTime_Total geben kann.

    Im Moment benutze ich diese Anweisung:
    Code:
    SELECT TOP 10 IsNull(ServiceTickets.Engineer_Displayname, 'Unbekannt'),
      Sum(ServiceTickets.TimeEntry_WorkingTime_Total) AS Stunden
    FROM ServiceTickets
    WHERE ServiceTickets.Duty_Ticket != '0' AND ServiceTickets.Response_SLA_Stopped_Date >= DateAdd(mm, 1, DateAdd(mm, 0, DateAdd(yy, DateDiff(yy, 0, '2013'), 0))) AND ServiceTickets.Response_SLA_Stopped_Date <= DateAdd(mm, 1, DateAdd(yy, DateDiff(yy, 0, '2013') + 1, -1))
    GROUP BY ServiceTickets.Engineer_Displayname
    ORDER BY Stunden DESC

    Da fehlt im Grunde genommen nur noch diese elfte Spalte für die Summe der übrigen werte der Tabelle.

    Ich hoffe ich verlange nicht zu viel, ich würde gerne im Nachhinein auch verstehen wie es funktioniert.
     
  8. ukulele

    ukulele Datenbank-Guru

    Brauchst du jetzt die 10 Engineer_Displayname mit der jeweils höchsten Summe alle Einträge oder die 10 höchsten Einträge gruppiert nach Engineer_Displayname? :confused:

    Code:
    SELECT    t.zeilennr,
            t.Engineer_Displayname,
            sum(t.TimeEntry_WorkingTime_Total) AS TimeEntry_WorkingTime_Total
    FROM    (
    
    SELECT    (    CASE
                WHEN    ROW_NUMBER() OVER (ORDER BY ServiceTickets.TimeEntry_WorkingTime_Total) <= 10
                THEN    ROW_NUMBER() OVER (ORDER BY ServiceTickets.TimeEntry_WorkingTime_Total)
                ELSE    11
                END ) AS zeilennr,
            ServiceTickets.Engineer_Displayname,
            ServiceTickets.TimeEntry_WorkingTime_Total
    FROM    ServiceTickets
    
    ) t
    GROUP BY t.zeilennr,t.TimeEntry_WorkingTime_Total
    
    Code:
    SELECT    t2.zeilennr,
            t2.Engineer_Displayname,
            sum(t2.TimeEntry_WorkingTime_Total) AS TimeEntry_WorkingTime_Total
    FROM    (
    
    SELECT    (    CASE
                WHEN    ROW_NUMBER() OVER (ORDER BY t1..TimeEntry_WorkingTime_Total) <= 10
                THEN    ROW_NUMBER() OVER (ORDER BY t1.TimeEntry_WorkingTime_Total)
                ELSE    11
                END ) AS zeilennr,
            t1..Engineer_Displayname,
            t1.TimeEntry_WorkingTime_Total
    FROM    (    SELECT    ServiceTickets.Engineer_Displayname,
                        sum(ServiceTickets.TimeEntry_WorkingTime_Total) AS TimeEntry_WorkingTime_Total
                FROM    ServiceTickets
                GROUP BY ServiceTickets.Engineer_Displayname ) t1
    
    ) t2
    GROUP BY t2.zeilennr,t2.TimeEntry_WorkingTime_Total
     
  9. Hazel4tw1

    Hazel4tw1 Aktiver Benutzer

    Also im Grunde genommen brauch ich die 10 Engineer_Displayname mit den 10 höchsten TimeEntry_WorkingTime_Total nachdem Engineer_Displayname gruppiert ist, da mehrer Stunden (TimeEntry_WorkingTime_Total) einem Techniker (Engineer_Displayname) zugeordnet werden kann. Und als elften Eintrag halt die Summe der restlichen Einträge in der Tabelle.



    Beidem oberenen Code kommt bei mir:

    Füge ich t.Engineer_Displayname noch unten in die Group By Klausel ein und ich meine Where Klausel (ServiceTickets.Duty_Ticket != '0') in die innere Abfrage mitstelle (nach dem 2ten From)
    bekomme ich 162 Einträge. die ersten 10 zählen in hoch in der zeilennr bis 10 hoch, danach kommen nur noch 11er.

    Mache ich Select Engineer_Displayname, Sum(TimeEntry_WorkingTime_Total) from ServiceTickets where ServiceTickets.Duty_Ticket != '0' group by Engineer_Displayname kommen 50 zeilen.
    eigentlich dürften es doch nicht mehr werden oder?


    der zweite code gibt bei mir 246 Zeilen aus auch vorher mit der Meldung
     
  10. Hazel4tw1

    Hazel4tw1 Aktiver Benutzer

    Habe vergessen bei dem zweiten Code von dir meine Where Klausel hinzuzufügen.

    Nun sind es auch 50 Einträge aber im Grunde genommen nichts anderes als Select Engineer_Displayname, Sum(TimeEntry_WorkingTime_Total) from ServiceTickets where ServiceTickets.Duty_Ticket != '0' group by Engineer_Displayname nur das eine zusätzliche Spalte da ist die von 1-10 hochzählt und dann nur noch 11 drin stehen hat.

    Zudem haben die 10 niedrigsten Werte die 1-10 eingetragen. (falls dir das hilft)
     
  11. ukulele

    ukulele Datenbank-Guru

    Ups, ein Punkt zuviel, eine falsche Spaltenangabe und DESC habe ich ergänzt. Geht das?
    Code:
    SELECT    t2.zeilennr,
            t2.Engineer_Displayname,
            sum(t2.TimeEntry_WorkingTime_Total) AS TimeEntry_WorkingTime_Total
    FROM    (
    
    SELECT    (    CASE
                WHEN    ROW_NUMBER() OVER (ORDER BY t1.TimeEntry_WorkingTime_Total DESC) <= 10
                THEN    ROW_NUMBER() OVER (ORDER BY t1.TimeEntry_WorkingTime_Total DESC)
                ELSE    11
                END ) AS zeilennr,
            t1.Engineer_Displayname,
            t1.TimeEntry_WorkingTime_Total
    FROM    (    SELECT    ServiceTickets.Engineer_Displayname,
                        sum(ServiceTickets.TimeEntry_WorkingTime_Total) AS TimeEntry_WorkingTime_Total
                FROM    ServiceTickets
                GROUP BY ServiceTickets.Engineer_Displayname ) t1
    
    ) t2
    GROUP BY t2.zeilennr,t2.Engineer_Displayname
     
  12. Hazel4tw1

    Hazel4tw1 Aktiver Benutzer

    Ja ideal, ich dank dir.

    Meinst du kannst mir noch bei einer anderen Sache helfen?

    Und zwar habe ich eine Abfrage die soweit auch funktioniert.

    Folgende Ergebnisse kommen dabei raus (auszug):

    [​IMG]


    Das Problem ist das ich quasi zu jedem Monat jeden Hersteller brauche, da aber keine Werte zu den Moanten gibt, zeigt er diese auch nicht an (was ja klar ist).

    Nach hin und her googeln bin ich dann auf UNION gestoßen, und so wie ich das verstanden habe füllt er dann
    Stunden und Anzahl mit nullen auf, oder? Leider bekomme ich das nicht hin (verstehe es nicht).

    Es wäre nett ukulele wenn du mir da nochmal weiter helfen könntest.
     
  13. akretschmer

    akretschmer Datenbank-Guru

    Wenn ich Dich recht verstehe, hast Du sowas wie:

    Code:
    test=*# select * from hazel ;
      datum  | wert
    ------------+------
    2014-01-05 |  10
    2014-01-15 |  20
    2014-03-25 |  30
    (3 rows)
    
    und suchst nun die Summe von wert je Monat. Da im Februar nix ist, wäre da kein datenatz, Du willst aber den Februar mit 0 sehen, oder?

    Code:
    test=*# select s, sum(wert) from (select generate_series(1,3) s) s left join hazel on s.s=extract(month from hazel.datum)::int group by 1;
    s | sum
    ---+-----
    1 |  30
    2 |
    3 |  30
    (3 rows)
    
    Du brauchst also eine Tabelle aller Monate, hier durch mich via generate_series(1,3) generiert. Die Spalte s ist der Monate, könnte man noch 'richtig' bennen, ...

    Nachtrag:

    Code:
    test=*# select s as monat, coalesce(sum(wert),0) from (select generate_series(1,3) s) s left join hazel on s.s=extract(month from hazel.datum)::int group by 1;
     monat | coalesce
    -------+----------
      1 |  30
      2 |  0
      3 |  30
    (3 rows)
    
     
  14. Hazel4tw1

    Hazel4tw1 Aktiver Benutzer

    Hallo,

    Nein, ich hab mich wohl etwas unglücklich ausgedrückt.

    Die Monate sind so oder so da. Es ist nur so, das z.B. im Juli (Monat 07) keine Stunden/Anzahl auf den Hersteller z.B. "Sikom" eingetragen wurden. Siehe Screenshot.

    Ich möchte aber das anstatt Sikom(oder auch andere Hersteller) im Monat 07 garnicht auftaucht, das es es auftaucht und dann unter Stunden und Anzahl eine Null steht.

    Hintergrund ist das ich damit ja Reporte erstellen muss, und wenn ich eine Kurven Diagramm erstelle er ja den Hersteller für den monat nicht hat, und somit die Kurve mitten im Diagramm anfängt (oder ähnliches)
     
  15. ukulele

    ukulele Datenbank-Guru

    Also ohne deinen Code jetzt zu zerlegen um zu verstehen was dort passiert kann ich dir eigentlich nur sagen das akretschmers Lösung unter MSSQL nicht läuft weil ich kein Gegenstück zu generate_series() kenne und es meines Wissens nach auch keines gibt.

    Ich würde vom Aufbau her so verfahren das ich mir alle Jahr / Monat / Hersteller-Kombinationen per Select hole und dazu dann die Summen joine. (Wenn es nicht zwangsläufig alle Kombinationen gibt muss man sich eine Tabelle mit den selbigen erstellen.)

    Hier mal grob:
    Code:
    SELECT    t1.Jahr,
            t1.Monat,
            t2.Hersteller,
            t3.Wert
    FROM    (    SELECT    DISTINCT
                        datepart(yyyy,t1.Datum) AS Jahr,
                        datepart(mm,t1.Datum) AS Monat
                FROM    tabelle ) t1,
            (    SELECT    DISTINCT
                        hersteller
                FROM    tabelle ) t2
    LEFT JOIN (    SELECT    Hersteller,
                        datepart(yyyy,t1.Datum) AS Jahr,
                        datepart(mm,t1.Datum) AS Monat,
                        sum(Wert) AS Wert
                FROM    tabelle
                GROUP BY Hersteller,
                        datepart(yyyy,t1.Datum),
                        datepart(mm,t1.Datum) ) t3
    ON        t1.Jahr = t3.Jahr
    AND        t1.Monat = t3.Monat
    AND        t1.Hersteller = t3.Hersteller
     
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