| Stuggling with SQL command [message #7558] |
Fri, 20 June 2003 23:39  |
Shahanur Rahman
Messages: 27
Registered: April 2003
|
Junior Member |
|
|
Hi Friends
I have a sql command which is giving result in two rows. my command is
select all m_bill.user_id,
sum(m_bill.bill_amount)+sum(m_bill.vat) bill,
sum(m_user.closing_balance) closing_balance,
sum(m_bill.paid_amount) paid,
sum(m_user.closing_balance)- sum(m_bill.paid_amount) remaining,
m_scheme.scheme_name
from m_bill, m_scheme, m_user
where ((m_user.scheme_id =m_scheme.scheme_id)
and (m_bill.user_id = m_user.user_id))
and m_bill.month_no=5 and m_bill.year=2003
and m_scheme.scheme_name !='Dhakacom Staff' and m_scheme.scheme_name !='Complimentary Account' and m_user.status='ACTIVE' and m_bill.user_id='abulm'
group by m_scheme.scheme_name,m_bill.bill_amount,m_bill.vat,m_user.closing_balance,m_bill.user_id order by m_scheme.scheme_name,m_bill.user_id
/
this query is giving me result :
USER_ID BILL CLOSING_BALANCE PAID REMAINING SCHEME_NAME
----------------------------------- --------- --------------- --------- --------- ------------------
abulm 0 .18 149 -148.82 No Use No Pay
abulm 148.91 .18 0 .18 No Use No Pay
But I want to show this query in one row like :
USER_ID BILL CLOSING_BALANCE PAID REMAINING
----------------------------------- --------- --------------- --------- ---------
SCHEME_NAME
--------------------------------------------------
abulm 148.91 .18 149 -148.82
No Use No Pay
If someone needs the tables structure i will provide them.
please help me.
|
|
|
|
|
|
| Re: Stuggling with SQL command [message #7641 is a reply to message #7638] |
Thu, 26 June 2003 04:29  |
Shahanur Rahman
Messages: 27
Registered: April 2003
|
Junior Member |
|
|
Dear Gilbey
Thank you very much for the reply. I was waiting eagerly for this, because i need it badly.
Here is the description of three table which I have used in this query.
SQL> desc m_bill
Name Null? Type
------------------------------- -------- ----
BILL_NO ---------- NUMBER(8)
USER_ID ---------- VARCHAR2(35)
MONTH_NO ---------- NUMBER(2)
YEAR ---------- NUMBER(4)
BILL_DATE ---------- DATE
BILL_AMOUNT ---------- NUMBER(12,2)
VAT ---------- NUMBER(12,2)
DISCOUNT_AMOUNT ---------- NUMBER(12,2)
PAID_AMOUNT ---------- NUMBER(12,2)
PAY_TYPE ---------- VARCHAR2(1)
BANK ---------- VARCHAR2(35)
BRANCH ---------- VARCHAR2(35)
CHECK_NO ---------- VARCHAR2(35)
NAME_ON_CARD ---------- VARCHAR2(35)
CARD_NAME ---------- VARCHAR2(35)
CARD_NO ---------- VARCHAR2(35)
BILL_DESCRIPTION ---------- VARCHAR2(50)
COMMENTS ---------- VARCHAR2(80)
SQL> desc m_scheme
Name Null? Type
------------------------------- -------- ----
SCHEME_ID ---------- NOT NULL VARCHAR2(20)
SCHEME_NAME ---------- VARCHAR2(50)
SCHEME_TYPE ---------- VARCHAR2(20)
SCHEME_STATUS ---------- VARCHAR2(20)
MONTHLY_FREE_HOURS ---------- NUMBER(6,2)
MONTHLY_MINIMUM_FEE ---------- NUMBER(12,2)
PER_MIN_COST ---------- NUMBER(12,4)
DISCOUNT_TYPE ---------- VARCHAR2(6)
DISCOUNT_FACTOR ---------- NUMBER(6,2)
DISCOUNT_AMOUNT ---------- NUMBER(12,2)
DISCOUNT_FREQ ---------- NUMBER(2)
DISCOUNT_RECURRING ---------- NUMBER(2)
SCHEME_GROUP ---------- VARCHAR2(20)
SQL> desc m_user
Name Null? Type
------------------------------- -------- ----
USER_ID ---------- NOT NULL VARCHAR2(30)
GROUP_ID ---------- VARCHAR2(30)
FIRST_NAME ---------- VARCHAR2(60)
LAST_NAME ---------- VARCHAR2(30)
NATIONALITY ---------- VARCHAR2(20)
DATE_OF_BIRTH ---------- DATE
ALT_EMAIL ---------- VARCHAR2(50)
RES_ADDRESS ---------- VARCHAR2(80)
RES_PHONE ---------- VARCHAR2(15)
RES_FAX ---------- VARCHAR2(15)
BILL_ADDRESS ---------- VARCHAR2(80)
STUDENTID ---------- VARCHAR2(30)
INST_NAME ---------- VARCHAR2(50)
SCHEME_ID ---------- VARCHAR2(20)
PASSWORD ---------- VARCHAR2(80)
CREATION_DATE ---------- DATE
ACTIVATION_DATE ---------- DATE
EXPIRE_DATE ---------- DATE
STATUS ---------- VARCHAR2(20)
CARD_NO ---------- VARCHAR2(50)
CARD_EXPIRY_DATE ---------- DATE
CARD_TYPE ---------- VARCHAR2(20)
DISCOUNT_TYPE ---------- VARCHAR2(6)
DISCOUNT_FACTOR ---------- NUMBER(6,2)
DISCOUNT_AMOUNT ---------- NUMBER(12,2)
DISCOUNT_FREQ ---------- NUMBER(2)
DISCOUNT_RECURRING ---------- NUMBER(2)
DHAKACOM_CRADIT_LIMIT ---------- NUMBER(12,2)
MAXIMUM_LOGIN ---------- NUMBER(2)
COMMENTS ---------- VARCHAR(200)
OPENING_BALANCE ---------- NUMBER(12,2)
CLOSING_BALANCE ---------- NUMBER(12,2)
UIN ---------- VARCHAR2(10)
PREPAID_TIME ---------- NUMBER(20)
|
|
|
|
|
|
| Re: Stuggling with SQL command [message #7662 is a reply to message #7644] |
Sat, 28 June 2003 01:48 |
Shahanur Rahman
Messages: 27
Registered: April 2003
|
Junior Member |
|
|
Dear Jigar
Thanks for the reply. I am sorry , I didn't understand your question. Do you want the query by which we insert the data in the table ? If so plz let me know. My data is in the table already which is posted by different users.
The purpose of this query is to design a report. Now my query is showing the data in two different rows. But I want them in one line by adding the specific lines as I have mentioned in my first post.
Thanks and waiting for your next reply.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
