group by statement

[#1]

Hi Experts

Ich habe the following load-statement and would like to group by jobnr:

SQL SELECT "auf_datum",

    bez as Leistungstext,

    jobnr,

    storniert,

    optional,

    verkauf_soll as VK_soll,

    verkauf_ist as VK_ist,

    einkauf_ist as EK_ist,

    auf_brutto as EK_soll,

FROM EASY.jleistg

WHERE    storniert = 0       

        and optional = 0;

How do I have to put the "group by jobnr" expression?

Thank you very much for your help!

Jan

[maleksafa · Accepted solution]

when you are using sql statement you can use the group by function after the where clause, in your case add group by jobnr after the where clause but you need to add some aggregation on the other fields for example

select max("auf_datum"), jobnr from EASY.jleist where storniert = 0 group by jobnr

the column in the select must either have an aggregation (max, min, sum, avg etc...) or included in the group by

[#3]

Okay, thank you. You mean like this:

FROM EASY.jleistg

WHERE    storniert = 0       

        and optional = 0 group by jobnr;

Thank you

[MarcoWedel]

Hallo Jan,

grouping only applies to aggregations.

Do you also plan to add a sum, count or other aggregating function?

Gruß Marco

[#5]

YEs, I do.

[maxgro]

fields not in group by needs a sum, count, .... (aggregation)

so you have to add some function for all bold fields

example  sum(storniert) or count(storniert)

SQL SELECT

    "auf_datum",

    bez as Leistungstext,

    jobnr,

    storniert,

    optional,

    verkauf_soll as VK_soll,

    verkauf_ist as VK_ist,

    einkauf_ist as EK_ist,

    auf_brutto as EK_soll

FROM EASY.jleistg

WHERE    storniert = 0      

        and optional = 0

group by jobnr

;