Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

group by statement

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

Tags (1)
1 Solution

Accepted Solutions
maleksafa
Valued Contributor

Re: group by statement

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

5 Replies
maleksafa
Valued Contributor

Re: group by statement

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

Not applicable

Re: group by statement

Okay, thank you. You mean like this:

FROM EASY.jleistg

WHERE    storniert = 0       

        and optional = 0 group by jobnr;

Thank you

Re: group by statement

Hallo Jan,

grouping only applies to aggregations.

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

Gruß Marco

Not applicable

Re: group by statement

YEs, I do.

MVP
MVP

Re: group by statement

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

;

Community Browser