Summe einer TeilAbfrage

[Hazel4tw1]

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)

SELECT IsNull(ServiceTickets.Engineer_Displayname, 'Unbekannt'),
Sum(ServiceTickets.TimeEntry_WorkingTime_Total) AS Stunden, MONTH(Resolution_SLA_Stopped_Date)as monat ,YEAR(Resolution_SLA_Stopped_Date) as jahr
FROM ServiceTickets
WHERE ServiceTickets.Duty_Ticket != '0'
GROUP BY ServiceTickets.Engineer_Displayname,Resolution_SLA_Stopped_Date
ORDER BY jahr

SELECT TOP 10 IsNull(ServiceTickets.Engineer_Displayname, 'Unbekannt'),
Sum(ServiceTickets.TimeEntry_WorkingTime_Total) AS Stunden
FROM ServiceTickets
WHERE ServiceTickets.Duty_Ticket != '0'
GROUP BY ServiceTickets.Engineer_Displayname
ORDER BY Stunden DESC

Select SUM(TimeEntry_WorkingTime_Total) from ServiceTickets
WHERE ServiceTickets.Duty_Ticket != '0'

SELECT Sum(ServiceTickets.TimeEntry_WorkingTime_Total) AS Stunden, ROW_NUMBER() over (Order by TimeEntry_WorkingTime_Total) as Row
FROM ServiceTickets
WHERE ServiceTickets.Duty_Ticket != '0'
group by TimeEntry_WorkingTime_Total

SELECT SUM(TimeEntry_WorkingTime_Total)
FROM ServiceTickets
WHERE ServiceTickets.Duty_Ticket != '0' and Unique_Id IN (SELECT TOP 10 Unique_Id
FROM ServiceTickets
ORDER BY TimeEntry_WorkingTime_Total desc)



SELECT TOP 10 Engineer_Displayname,TimeEntry_WorkingTime_Total
FROM ServiceTickets
ORDER BY TimeEntry_WorkingTime_Total desc



Select SUM(TimeEntry_WorkingTime_Total)- stunden as Test from (SELECT TOP 10
Sum(ServiceTickets.TimeEntry_WorkingTime_Total) AS stunden
FROM ServiceTickets
WHERE ServiceTickets.Duty_Ticket != '0'
GROUP BY TimeEntry_WorkingTime_Total
ORDER BY Stunden DESC)



Select (ganz - garnicht) as summe
from (select TimeEntry_WorkingTime_Total as ganz, test as garnicht
from (select TOP 10 TimeEntry_WorkingTime_Total as test
from ServiceTickets order by test desc))


Select SUM(TimeEntry_WorkingTime_Total) as stunden, Engineer_Displayname
FROM ServiceTickets
WHERE ServiceTickets.Duty_Ticket != '0'
group by Engineer_Displayname
order by stunden desc

SELECT IsNull(ServiceTickets.Engineer_Displayname, 'Unbekannt'),
Sum(ServiceTickets.TimeEntry_WorkingTime_Total) AS Stunden
FROM ServiceTickets
WHERE ServiceTickets.Duty_Ticket != '0'
GROUP BY ServiceTickets.Engineer_Displayname
ORDER BY Stunden DESC

 

[akretschmer]

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)

Vielleicht so:

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.

 

[Hazel4tw1]

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.

 

[akretschmer]

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.

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

 

[ukulele]

Es gibt vermutlich viele Wege aber mein Favorit wäre soetwas hier:

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.

 

[Hony%]

Hi.

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.

 

[Hazel4tw1]

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:

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.

 

[ukulele]

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?

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

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

 

[Hazel4tw1]

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:

Msg 8120, Level 16, State 1, Line 2
Column 't.Engineer_Displayname' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

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

Msg 8120, Level 16, State 1, Line 2
Column 't2.Engineer_Displayname' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

 

[Hazel4tw1]

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)

 

[ukulele]

Ups, ein Punkt zuviel, eine falsche Spaltenangabe und DESC habe ich ergänzt. Geht das?

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

 

[Hazel4tw1]

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.

SELECTLTrim(RTrim(Tempo.Hersteller1)) AS Manufacturer,
Count(DISTINCT Tempo.Number1) AS Anzahl,Sum(Tempo.Stunden1) AS Stunden,Right('0' + CONVERT(varchar(2),Tempo.Monat1), 2) AS Monat,
Tempo.Jahr1
FROM (SELECTCASEWHEN ServiceTickets.Manufacturer = '- other'THEN'Andere'WHEN ServiceTickets.Manufacturer ISNULLTHEN'Andere'ELSE (SELECT ServiceTickets.Manufacturer) ENDAS Hersteller1,
ServiceOrder.Time_Total_Sum AS Stunden1,
ServiceOrder.Number AS Number1,
Month(ServiceOrder.Creation_Date) AS Monat1,Year(ServiceOrder.Creation_Date) AS Jahr1FROM ServiceTicketsINNERJOIN ServiceOrder ON ServiceTickets.Unique_Id = ServiceOrder.Container_Unique_IdWHERE 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)) AND ServiceTickets.Duty_Ticket != '0') AS Tempo
GROUPBY Tempo.Jahr1,
Tempo.Monat1,
Tempo.Hersteller1
ORDERBY Tempo.Jahr1,
Tempo.Monat1

Folgende Ergebnisse kommen dabei raus (auszug):




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.

 

[akretschmer]

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.

Wenn ich Dich recht verstehe, hast Du sowas wie:

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?

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:

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)

 

[Hazel4tw1]

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)

 

[ukulele]

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:

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