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? Mon, 30 June 2008 15:04
 hunterdong Messages: 1Registered: 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
 BlackSwan Messages: 25524Registered: January 2009 Location: SoCal Senior Member
http://www.orafaq.com/forum/t/88153/0/
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: 5933Registered: 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,'milk',3,'2nd',2,'Drink');
insert into trans values ('02Jan2008_t003','John001',2,'butter',1,'1st',2,'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: Fri Jul 21 10:18:58 CDT 2017

Total time taken to generate the page: 0.08094 seconds