List all customer (user id, name, and e-mail), number of charging

List all customer (user id, name, and e-mail), number of charging, total charging,
number of transaction and total number of transaction value?

If u have these tables:

1- 'Credit_card'
columns: credit_number,type,value,Customer_ID.

2-'Coupon'
columns: Coupon_number,value,password,Customer_ID

3-'Customer'
columns: Customer_ID,Fname,Mname,lname,E_mail,password,bdate,tot_trans,mobil_no,
hom_no,ex_id_date,current_Balance.

mnmlove
Profile Answers by mnmlove Questions by mnmlove
Jan 15th, 2008
1
2438

Questions by mnmlove

SQL Plus

Answer

Showing Answers 1 - 1 of 1 Answers

friends_mukesh
Profile Answers by friends_mukesh

Feb 19th, 2008

Hi, One of the solution is as follow:
select cust.customer_id,fname,mname,lname,e_mail,credit.tot_credit+coupan.tot_coup no_of_recharge,
credit.tot_credit_val+coupan.tot_coup_val tot_recharging,tot_trans,credit.tot_credit_val+coupan.tot_coup_val-current_balance transaction_val,cust.current_balance
from Customer cust ,
(select customer_id,count(value) tot_credit,sum(value) tot_credit_val from credit_card group by customer_id) credit,
(select customer_id,count(value) tot_coup,sum(value) tot_coup_val from coupan group by coupan.customer_id) coupan
where coupan.customer_id=cust.customer_id and cust.customer_id=credit.customer_id;