Another 'buckets' question

  • Thread starter Thread starter elsenorjose
  • Start date Start date
E

elsenorjose

I have a table with the following structure:

user_id
transaction_type
revenue_amt
items_sold

I have multiple rows for each user_id with a trans_type code and the
revenue they generated and number of items they sold per
transaction_type.

What I need to do is roll up the revenue for each user into one summary
row. I want to sum the revenue and items_sold for each of the different
trans_types into 2 buckets; 'full' and 'discount'. What I would end up
with is one row per user with the sum of revenue and sum of items for
'full' and 'discount' types:

user_id
full_revenue
discount_revenue
full_items_sold
discount_items_sold

I'm not very good with the IIf statement and I've been trying to use it
but end up with a column called 'Expr1' with 'Full' or 'Discount' in
it.

HELP!

Thanks!
 
I have a table with the following structure:

user_id
transaction_type
revenue_amt
items_sold

I have multiple rows for each user_id with a trans_type code and the
revenue they generated and number of items they sold per
transaction_type.

What I need to do is roll up the revenue for each user into one summary
row. I want to sum the revenue and items_sold for each of the different
trans_types into 2 buckets; 'full' and 'discount'. What I would end up
with is one row per user with the sum of revenue and sum of items for
'full' and 'discount' types:

user_id
full_revenue
discount_revenue
full_items_sold
discount_items_sold
Click to expand...


SELECT user_id,
Sum(IIf(transaction_type = "Full", revenue_amt, 0))
As full_revenue
Sum(IIf(transaction_type = "Full", items_sold, 0))
As full_items_sold
Sum(IIf(transaction_type = "Discount", revenue_amt, 0))
As full_revenue
Sum(IIf(transaction_type = "Discount", items_sold, 0))
As discount_items_sold
FROM thetable
FROUP BY user_id
 
What I would try is to do a Sum query and Sum two separate rows. The fields
that you're not summing by should be included as Group By, provided that you
actually want to Group By them. If not, then they shouldn't be included in
the query, as this will be considered a domain aggregate function.

If you're trying to Sum two columns together, this can probably be
accomplished by something like this (in the SQL view):

Sum([full_revenue] + [discount_revenue]) AS TotalRevenue

Note that you don't have to use addition here; if you want you could
multiplication or any other numeric operand.

-Chris
 
Back
Top