Home » SQL & PL/SQL » SQL & PL/SQL » derive sub-total of one column and divide by another column's distinct value?
derive sub-total of one column and divide by another column's distinct value? [message #330677] |
Mon, 30 June 2008 15:04  |
hunterdong
Messages: 1 Registered: June 2008
|
Junior Member |
|
|
Hi dear all. I want to calculate a sub total per customer and divide it by numbers of transactions of which each has one item per row. Data is in a transaction table and i can use interim tables if needed.
Basically I want to derive customer table (customerID as PK and other columns are average items bought per trans and summarized product groups etc) from Transaction table (per item/per line), tried a piece of SQL code. It can derives how many items of a certain group are bought by that customer, but I didn't work out how to derive average items bought per transaction (e.g. can't get the sum of transactions for that customer and can't get the sum of the GroupX_item_sum).
See table example in attached .csv
**************************
Facts
Day1:
John001 (an identifiable customer) bought 2 packs of bread and 3 bottles of milk and paid 8 pounds;
Hunter002 bought 2 packs of bread and paid 2 pounds; (1st transaction)
Day2:
John001 again bought 1 box of butter and paid 2 pounds (2nd transaction)
Hunter001 bought 2 bread and paid 2 pounds (3rd transaction)
Day3
John001 again bought 1 bread and and 1 butter and paid 3 pounds (4th transction)
*************
Table:
All I got is the transaction table attached
A table that one customer per line is needed for analysis purpose. If possible this table should be created automatically (product-category fields read from this transaction table or a product table) but can be hard-coded in SQL decode() like below as well:
John001's FOOD_QTY-Bought is 5, which = 2 (bread) + 1 (butter) +1 (bread) +1 (butter)
John001's Transaction-contain-food is 3, which is 1(01Jan2008_t001) + 1 ( 02Jan2008_t0034) +1 (03Jan2008_t004)
John001's average item number per transaction is 5(food) +3(drink)/3 = 2.66
The SQL I got is:
create table custsummary as
select custid,
sum(Item_Qty)/count(*) Avg-item-num-per-trans,
//THIS line does NOT derive correct result as count(*) is sum of lines, not sum of transactions
sum(decode(Item_Group,'Food',Item_Qty,0)) FOOD_QTY-Bought,
sum(decode(Item_Group,'Food',1,0)) Transaction-contain-food,
sum(decode(Item_Group,'Drink',Item_Qty,0)) DRINK_QTY-Bought,
sum(decode(Item_Group,'Drink',1,0)) Transaction-contain-drink,
sum(decode(Item,'ProdXX',Item_Qty,0)) ProdXX_QTY-Bought,
sum(decode(Item,'ProdXX',1,0)) Transaction-contain-ProdXX
from transactiontable
group by custid;
If I use sum(Item_Qty)/count(distinct(TransID), will this work?
Any ideas appreciated
|
|
|
|
Re: derive sub-total of one column and divide by another column's distinct value? [message #330807 is a reply to message #330677] |
Tue, 01 July 2008 06:31  |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Don't mind the natives - they're grumpy.
For future reference, try to post a test case with your question, ie a script that creates the tables you need and loads the data, so all we have to worry about is the SQL.
Something like this:
create table trans (TransID varchar2(30)
,CustID varchar2(30)
,TotalPayment_transaction number
,Item varchar2(30)
,Item_Qty number
,Item_Seq_inTrans varchar2(30)
,Item_Unit_Price number
,Item_Group varchar2(30));
insert into trans values ('01Jan2008_t001','John001',8,'Bread',2,'1st',1,'Food');
insert into trans values ('01Jan2008_t001','John001',8,'milk',3,'2nd',2,'Drink');
insert into trans values ('01Jan2008_t002','Hunter001',2,'Bread',2,'1st',1,'Food');
insert into trans values ('02Jan2008_t003','John001',2,'butter',1,'1st',2,'Food');
insert into trans values ('03Jan2008_t004','John001',3,'bread',1,'1st',1,'Food');
insert into trans values ('03Jan2008_t004','John001',3,'butter',1,'2nd',2,'Food');
select custid,count(*),
sum(Item_Qty)/count(*) Avg_item_num_per_trans_orig,
sum(Item_Qty)/count(distinct transid) Avg_item_num_per_trans_new,
sum(decode(Item_Group,'Food',Item_Qty,0)) FOOD_QTY_Bought,
sum(decode(Item_Group,'Food',1,0)) Transaction_contain_food,
sum(decode(Item_Group,'Drink',Item_Qty,0)) DRINK_QTY_Bought,
sum(decode(Item_Group,'Drink',1,0)) Transaction_contain_drink,
sum(decode(Item,'ProdXX',Item_Qty,0)) ProdXX_QTY_Bought,
sum(decode(Item,'ProdXX',1,0)) Transaction_contain_ProdXX
from trans
group by custid;
The answer to your question is 'Yes'. Distinct TransID will do the job.
Why didn't you just test it yourself?
|
|
|
Goto Forum:
Current Time: Sun Feb 09 20:29:46 CST 2025
|