5 Replies Latest reply: Oct 31, 2013 9:47 AM by Yaniv Egozi RSS

    count and sum

    Yaniv Egozi

      G'day,

      I need your help in a small problem i'm stuck with.

       

      I have a sales table that show the invoice_no, worker_no, product_id, total_amount

      100, 555, 99889 , 70

      100,555,99888,100

      100,666,99999,50

      101,555,77887,650

      102,555,77777,250

      103,666,78956,199

       

       

      I want the create a table that shows the amount of invoices a worker have when the sales (of the invoice) is between 0 to 200, and the number of invoices between 201 to 500 and above 501.

      the table should be like that

      worker_no, #of invoices between 0 to 200, #of invoices between 201 to 500, #of invoices above 501

      555,1,1,1

      666,2,0,0

       

      When I am adding the invoice_no to the table it works fine but when I take it out I am it sum all the invoices and not per invoice so I am  getting:

      555,0,0,3

      666,0,2,0

       

      I am using the following code:

      if(sum(total <invoice_no,worker_no> total_amount) > 0 and sum(total <invoice_no,worker_no> total_amount) < 200 , (COUNT (DISTINCT total <invoice_no, worker_no> invoice_no)),0)

       

      Thanks in advance
      .