Home » SQL & PL/SQL » SQL & PL/SQL » derive sub-total of one column and divide by another column's distinct value?
icon5.gif  derive sub-total of one column and divide by another column's distinct value? [message #330677] Mon, 30 June 2008 15:04 Go to next message
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 #330679 is a reply to message #330677] Mon, 30 June 2008 15:10 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
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 Go to previous message
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?
Previous Topic: Statistics locked
Next Topic: how to get data between from previous year April to given month and year
Goto Forum:
  


Current Time: Sun Feb 09 20:29:46 CST 2025