Home » SQL & PL/SQL » SQL & PL/SQL » Help With Report Format
Help With Report Format [message #245275] Fri, 15 June 2007 13:25 Go to next message
deay
Messages: 51
Registered: August 2005
Member
I have been asked to design a report query that displays all the
States by Rows then shows Income and Counts by fundcode.

here is the query:

select sum(g.payamount) Income, count(g.payamount) Count, a.state State, g.fundcode FundCode
from payment g, address a
where g.paydate between to_date('01-JAN-2006', 'DD-MON-YYYY') and to_date('31-DEC-2006','DD-MON-YYYY')
and g.idnumber = a.idnumber
and g.payamount>0
group by a.state, g.fundcode;


here is a sample of the Sql output:

IA
FUNDCODE COUNT INCOME
DH 6,171 $141,400.81
DK 1,759 $18,506.57
DN 859 $18,689.85
IL
FUNDCODE COUNT INCOME
DH 17,259 $503,757.19
DK 4,742 $54,221.71
DN 4,262 $108,132.58
IN
FUNDCODE COUNT INCOME
DH 11,121 $318,484.67
DK 3,399 $38,990.99
DN 3,015 $53,524.37


mgt wants the report to be formatted this way:


                  DH                  DK                DN    
State       Count    Income      Count   Income    Count   Income

IA          6,171   $141,400.81  1,759   $18,506.57  859    $18,689.85

IL         17,259   $503,757.19   4,742  $54,221.71  4,262  $108,132.58

IN         11,121   $318,484.67   3,399  $38,990.99  3,015   $53,524.37


appreciate any help/tips on how to get the report to output the fundcode as headers with the corresponding count/income to crosstab across the column headers.

thanks.





Re: Help With Report Format [message #245279 is a reply to message #245275] Fri, 15 June 2007 13:42 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
search on this forum for PIVOT QUERY
Re: Help With Report Format [message #245293 is a reply to message #245275] Fri, 15 June 2007 14:43 Go to previous messageGo to next message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
Check out SQL Snippets: SQL Techniques Tutorials - Rows to Columns.

--
Joe Fuda
SQL Snippets
Re: Help With Report Format [message #245701 is a reply to message #245293] Mon, 18 June 2007 10:11 Go to previous messageGo to next message
deay
Messages: 51
Registered: August 2005
Member
thanks SnippetyJoe...like your website and found your scripts
easy to read and use.

I got the pivot query to work however I am missing one component
and that is the individual count(g.payamount) by fundcode.

how would I go about including the individual Count() within the Sum() so that I get count(payments) included with sum(payments), the aggregate totals are generated but I need the TotCount result broken out by fundcode.

thanks again.

SQL> select a.state State,
  2  sum( decode( g.fundcode, 'DH', g.payamount, null ) ) as DH ,
  3  sum( decode( g.fundcode, 'DK', g.payamount, null ) ) as DK ,
  4  sum( decode( g.fundcode, 'DN', g.payamount, null ) ) as DN,
  5  count(g.payamount) as TotCount, sum(g.payamount) TotIncome  
  6  from payment g, address a
  7  where g.paydate between '01-JAN-2006' and '31-DEC-2006'
  8  and g.idnumber = a.idnumber
  9  and g.payamount>0
 10  and a.state in('PA','VA','WA')
 11  and g.fundcode in ('DH','DK','DN')
 12  group by a.state;

ST         DH         DK         DN   TOTCOUNT  TOTINCOME
-- ---------- ---------- ---------- ---------- ----------
PA  946732.61   72871.66  126222.87      25641 1145827.14
VA  474414.74   38276.39  151771.11      12798  664462.24
WA  568829.99    50321.7  179592.26      17391  798743.95
Re: Help With Report Format [message #245703 is a reply to message #245275] Mon, 18 June 2007 10:16 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
TRY

select a.state State,
sum( decode( g.fundcode, 'DH', g.payamount, null ) ) as DH ,
sum( decode( g.fundcode, 'DH', 1, 0 ) ) as CNT_DH ,
sum( decode( g.fundcode, 'DK', g.payamount, null ) ) as DK ,
sum( decode( g.fundcode, 'DK', 1, 0 ) ) as CNT_DK ,
sum( decode( g.fundcode, 'DN', g.payamount, null ) ) as DN,
sum( decode( g.fundcode, 'DN', 1, 0 ) ) as CNT_DN,
count(g.payamount) as TotCount, sum(g.payamount) TotIncome  
from payment g, address a
where g.paydate between '01-JAN-2006' and '31-DEC-2006'
and g.idnumber = a.idnumber
and g.payamount>0
and a.state in('PA','VA','WA')
and g.fundcode in ('DH','DK','DN')
group by a.state;
Re: Help With Report Format [message #245713 is a reply to message #245703] Mon, 18 June 2007 10:50 Go to previous messageGo to next message
deay
Messages: 51
Registered: August 2005
Member
thanks Bill worked like a charm.

ST         DH     CNT_DH         DK     CNT_DK         DN     CNT_DN   TOTCOUNT  TOTINCOME
-- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
PA  946732.61      20119   72871.66       3201  126222.87       2321      25641 1145827.14
VA  474414.74       9143   38276.39       1338  151771.11       2317      12798  664462.24
WA  568829.99      12436    50321.7       1876  179592.26       3079      17391  798743.95

SQL> 
Re: Help With Report Format [message #245734 is a reply to message #245703] Mon, 18 June 2007 12:28 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Bill B wrote on Mon, 18 June 2007 11:16
TRY

where g.paydate between '01-JAN-2006' and '31-DEC-2006'



I'm a little surprised to see that method from you Bill.
icon12.gif  Re: Help With Report Format [message #245751 is a reply to message #245734] Mon, 18 June 2007 13:02 Go to previous messageGo to next message
deay
Messages: 51
Registered: August 2005
Member
Joy

that's not Bill's code that's mine and I am well aware of using
the to_date function as illustrated in my initial post.

I got lazy with my test code since I already knew both versions
produce the same results.... ;~)



[Updated on: Mon, 18 June 2007 13:04]

Report message to a moderator

Re: Help With Report Format [message #245762 is a reply to message #245275] Mon, 18 June 2007 13:18 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Thanks you for defending me. I didn't even notice that portion of code.
Re: Help With Report Format [message #245776 is a reply to message #245762] Mon, 18 June 2007 14:40 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Ooops, and it's my fault for the oversight. I usually backtrack to see where it first showed up and I could swear, I really mean it, that I didn't see it anywhere except in Bill's post. Heh, I wonder how I missed that. I didn't think Bill would do that. 1 lash with a wet noodle for me.
Re: Help With Report Format [message #245977 is a reply to message #245701] Tue, 19 June 2007 08:31 Go to previous message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
Thanks for the feedback about my site deay. Glad you found it helpful.

It looks like you already have an answer to your question about count(g.payamount) by fundcode so I'll leave it at that.

--
Joe Fuda
SQL Snippets
Previous Topic: ORA-01940: cannot drop a user that is currently connected
Next Topic: how can i determine the size of a table?
Goto Forum:
  


Current Time: Sat Dec 03 20:00:06 CST 2016

Total time taken to generate the page: 0.04057 seconds