Sum total issue

[egoziyan]

G'day,

i need your advice with an issue I'm dealing with.

I want to combine the number of invoices into group of sales (group 1 - number of invoices between 0 - 200 $, group 2 number of invoices between 201 - 300 , group 3  number of invoices abouve $300)

My data is:

store, worker, invoice_no, sa_total_without_tax, month

11,jhon, 10055,170,3

11,jhon,10056,180,3

11,jhon,10057,280,3

11,jhon,10058,400,3

11,Peter,10059,180,3

11,Peter,10060,200,3

11,Peter,10061,250,3

the result should be like

Month	Worker	Group1	Group2	Group3
3	jhon	2	1	1
3	Peter	1	2	0

My problem is that I am getting all the invoices into group 3 when I am droping the invoice_no dimention.

my expression is

Group1 -

(sum(total <INVOICE_NO, Worker> SA_TOTAL_WITHOUT_TAX) > 0 and sum(total <INVOICE_NO, Worker> SA_TOTAL_WITHOUT_TAX) < 200 , (COUNT (DISTINCT total <SA_INVOICE_NO, WorkerName> SA_INVOICE_NO)),0))

Group2 -

(sum(total <INVOICE_NO, Worker> SA_TOTAL_WITHOUT_TAX) > 200 and sum(total <INVOICE_NO, Worker> SA_TOTAL_WITHOUT_TAX) < 300 , (COUNT (DISTINCT total <SA_INVOICE_NO, WorkerName> SA_INVOICE_NO)),0))

Group3 -

(sum(total <INVOICE_NO, Worker> SA_TOTAL_WITHOUT_TAX) > 300 , (COUNT (DISTINCT total <SA_INVOICE_NO, WorkerName> SA_INVOICE_NO)),0))

Thanks in advance

Yaniv

[Gysbert_Wassenaar]

Create the groups in the script instead:

LOAD

    store,

    worker,

    invoice_no,

    sa_total_without_tax,

    month,

    if(sa_total_without_tax<200,'Group1',

        if(sa_total_without_tax<300,'Group2','Group3')) as Group

FROM ...somesource...;