iif Calculate percentage (1 Viewer)

yanliang

New member
Local time
Today, 09:53
Joined
Sep 10, 2020
Messages
7
I need to calculate the percentage of the contribution of customers (how many times of purchases) in a specific state.
iff function: count up the times of transection of customers in a state like "Indiana" and divide to the total numbers of transactions.
Pls help
thank you
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:53
Joined
May 7, 2009
Messages
19,169
Create a Total query against your table(query1):

Select customer, state, count(state) as countofstate from table group by customer, state;

Create another Total query, only to the total count per customer(query2):

Select customer, count(customer) as exp1 from table group by customer;

Create the final query linking the 2 query above by customer field:

Select a.customer, b.countofstate, formatpercent(b.countofstate/a.expr1) as pct from
Query2 as a inner join query1 as b on a.customer=b.customer;
 

Users who are viewing this thread

Top Bottom