| complex sql query [message #20082] |
Mon, 29 April 2002 09:15  |
Malathi_Ashok
Messages: 1
Registered: April 2002
|
Junior Member |
|
|
I have a table which has the following Data.
BILL_PAY_DATE PAYMENT_TYPE PAYMENT_AMOUNT ST
---------- ---------- - ---------- --------------
2002-04-29 CREDIT 200 ALABAMA
2002-04-27 DEBIT 300 ALABAMA
2002-04-28 DEBIT 100 ALABAMA
I need to generate a report which looks like this
State $TotalSales #of Debit Pmts. # ofCash Pmts Total# of Pmts.
Alabama 409.25 10 9 19
Alabama 409.25 10 9 19
Alabama 409.25 10 9 19
Alabama 409.25 10 9 19
Can I write a single sql to get this data from the above table.
thanx in advance
Malathi
|
|
|
|
| Re: complex sql query [message #20083 is a reply to message #20082] |
Mon, 29 April 2002 10:14  |
Jon
Messages: 483
Registered: May 2001
|
Senior Member |
|
|
Not exactly sure of how cash, debit, and credit relate, but something along these lines should be a starting point:
SELECT STATE,
SUM(DECODE(TYPE,'CREDIT',(AMOUNT*-1),'DEBIT',AMOUNT)) "$TotalSales",
SUM(DECODE(TYPE,'DEBIT',1,0)) "#of Debit Pmts.",
SUM(DECODE(TYPE,'CREDIT',1,0)) "#of Cash Pmts",
COUNT(TYPE) "Total# of Pmts." FROM PAYMENT
GROUP BY STATE;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
