Count items for invoice

[dimak123]

Good day,

need some help with aggregations:

I want to show a list of clients and to see how many invoices(just count) contained only one item, 2 items, 3 items and 4.

this is my data:

CustomerId	InvoiceId	InvoiceLine	ItemId	Quantity
100237	01390082_INV	3	10692016	2
100237	01390082_INV	4	10692016	1
100237	01390082_INV	5	10692016	2
100237	01390082_INV	6	10692016	2
100237	01390082_INV	7	10692016	2
100237	01390082_INV	8	10692016	2
100083	01390090_INV	1	10281004	4
100083	01390090_INV	2	10692016	1
100083	01390090_INV	3	10281004	2
100083	01390090_INV	4	10281004	2
100083	01390090_INV	5	10281004	1
100083	01390090_INV	6	10281004	4
100083	01390090_INV	7	10281004	4

many thanks a head

[MayilVahanan]

Hi

Try like this

Customer, Invoice Id as dimension

Count(distinct ItemId) as Measure

So that you can able to find, each invoices has how many item count.

[dimak123]

i need to show i my final table only clients and counts , without invoices

[MayilVahanan]

Hi

Could you please say the output how you expected for the sample data.

[dimak123]

my expected result
CustomerId	1 item for Invoice	2 items for Invoice	3 items for Invoice	4 items for Invoice
100237	10	5	4	1
100083	15	7	2	1

[PradeepReddy]

see the attachment, it might be helpful for you..

[dimak123]

thank you, but the itemid doesn't really matter only the Quantity field

[PradeepReddy]

how you are calculating  '1 item for Invoice' as  10 for customer ID=100237?

[dimak123]

the numbers i put its not real

just the structure

[sasikanth]

hi ,

please find the attachment ,

seems your expected output is wrong according to the sample data