Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need totals per month with the following select 1

Status
Not open for further replies.
grapes12

grapes12

Technical User
Mar 2, 2010
124
ZA
This is what i currently have...but not what i am looking for...
CODE
select creation_date,
sum(DECODE(to_char(creation_date, 'MM/DD/YYYY HH24:MI:SS'),'01', 1,0)) JAN,
sum(DECODE(to_char(creation_date, 'MM/DD/YYYY HH24:MI:SS'),'02', 1,0)) FEB,
sum(DECODE(to_char(creation_date, 'MM/DD/YYYY HH24:MI:SS'),'03', 1,0)) MAR,
sum(DECODE(to_char(creation_date, 'MM/DD/YYYY HH24:MI:SS'),'04', 1,0)) APR,
sum(DECODE(to_char(creation_date, 'MM/DD/YYYY HH24:MI:SS'),'05', 1,0)) MAY,
sum(DECODE(to_char(creation_date, 'MM/DD/YYYY HH24:MI:SS'),'06', 1,0)) JUN,
sum(DECODE(to_char(creation_date, 'MM/DD/YYYY HH24:MI:SS'),'07', 1,0)) JUL,
sum(DECODE(to_char(creation_date, 'MM/DD/YYYY HH24:MI:SS'),'08', 1,0)) AUG,
sum(DECODE(to_char(creation_date, 'MM/DD/YYYY HH24:MI:SS'),'09', 1,0)) SEP,
sum(DECODE(to_char(creation_date, 'MM/DD/YYYY HH24:MI:SS'),'10', 1,0)) OCT,
sum(DECODE(to_char(creation_date, 'MM/DD/YYYY HH24:MI:SS'),'11', 1,0)) NOV,
sum(DECODE(to_char(creation_date, 'MM/DD/YYYY HH24:MI:SS'),'12', 1,0)) DEC,
count(*) TOTAL
from applsys.fnd_user
where creation_date > to_date('01/01/2010','MM-DD-YYYY')
GROUP BY creation_date
order by 1;
/CODE

I need the amount of users added to be displayed per month..together with a total...
output similar to this:
JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC TOTAL
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---
4 0 1 0 0 0 0 0 0 0 0 0 5
0 3 0 0 0 0 0 0 0 0 0 0 3
TOTAL 4 3 1 0 0 0 0 0 0 0 0 0 8

Here is what the columns and data looks like:
COLUMNS:
"USER_ID","USER_NAME", "LAST_UPDATE_DATE", "LAST_UPDATED_BY", "CREATION_DATE", "CREATED_BY","LAST_UPDATE_LOGIN","SESSION_NUMBER","START_DATE","END_DATE","DESCRIPTION",
DATA:
2289, CECILIA.BUTHELEZI@MACBRIGHTBAR.CO.ZA, 3/24/2010 12:37:23 PM, 2289, 1/18/2010 12:22:49 PM, 1295, 4975366, 24, 1/18/2010, ,Cecilia Buthelezi,4/6/2010 8:46:36 AM,3/24/2010 12:37:23 PM
Need help with the script please!


 
Sort by date Sort by votes
Try this - restricted to just 4 months but you
get the idea,

select
sum(decode(to_char(creation_date,'MM'),'01',1,0)) JAN,
sum(decode(to_char(creation_date,'MM'),'02',1,0)) FEB,
sum(decode(to_char(creation_date,'MM'),'03',1,0)) MAR,
sum(decode(to_char(creation_date,'MM'),'04',1,0)) APR
from find_user
group by creation_date
union all
select
sum(decode(to_char(creation_date,'MM'),'01',1,0)) JAN,
sum(decode(to_char(creation_date,'MM'),'02',1,0)) FEB,
sum(decode(to_char(creation_date,'MM'),'03',1,0)) MAR,
sum(decode(to_char(creation_date,'MM'),'04',1,0)) APR
from find_user



In order to understand recursion, you must first understand recursion.
 
Upvote 0 Downvote
Thanks..but what about the totals...i only need totals for this year...
code
select
sum(DECODE(to_char(creation_date, 'MM'),'01', 1,0)) JAN,
sum(DECODE(to_char(creation_date, 'MM'),'02', 1,0)) FEB,
sum(DECODE(to_char(creation_date, 'MM'),'03', 1,0)) MAR,
sum(DECODE(to_char(creation_date, 'MM'),'04', 1,0)) APR,
sum(DECODE(to_char(creation_date, 'MM'),'05', 1,0)) MAY,
sum(DECODE(to_char(creation_date, 'MM'),'06', 1,0)) JUN,
sum(DECODE(to_char(creation_date, 'MM'),'07', 1,0)) JUL,
sum(DECODE(to_char(creation_date, 'MM'),'08', 1,0)) AUG,
sum(DECODE(to_char(creation_date, 'MM'),'09', 1,0)) SEP,
sum(DECODE(to_char(creation_date, 'MM'),'10', 1,0)) OCT,
sum(DECODE(to_char(creation_date, 'MM'),'11', 1,0)) NOV,
sum(DECODE(to_char(creation_date, 'MM'),'12', 1,0)) DEC
-- count(*) TOTAL
from applsys.fnd_user
where creation_date > to_date('01/01/2010','MM-DD-YYYY')
GROUP BY creation_date
union all
select
sum(DECODE(to_char(creation_date, 'MM'),'01', 1,0)) JAN,
sum(DECODE(to_char(creation_date, 'MM'),'02', 1,0)) FEB,
sum(DECODE(to_char(creation_date, 'MM'),'03', 1,0)) MAR,
sum(DECODE(to_char(creation_date, 'MM'),'04', 1,0)) APR,
sum(DECODE(to_char(creation_date, 'MM'),'05', 1,0)) MAY,
sum(DECODE(to_char(creation_date, 'MM'),'06', 1,0)) JUN,
sum(DECODE(to_char(creation_date, 'MM'),'07', 1,0)) JUL,
sum(DECODE(to_char(creation_date, 'MM'),'08', 1,0)) AUG,
sum(DECODE(to_char(creation_date, 'MM'),'09', 1,0)) SEP,
sum(DECODE(to_char(creation_date, 'MM'),'10', 1,0)) OCT,
sum(DECODE(to_char(creation_date, 'MM'),'11', 1,0)) NOV,
sum(DECODE(to_char(creation_date, 'MM'),'12', 1,0)) DEC
from applsys.fnd_user;
is my where clause correct..
 
Upvote 0 Downvote
Need this

where creation_date > to_date('01/01/2010','MM-DD-YYYY')

in the 2nd select too


In order to understand recursion, you must first understand recursion.
 
Upvote 0 Downvote
yes that worked..but its giving me double totals...how can i just get one total from the union...
 
Upvote 0 Downvote
Actually i need a totals column..where do i fit that in?
 
Upvote 0 Downvote
I've kind of lost what you're after now but if you need a total which totals horzontally just add up all your columns

e.g

select
sum(DECODE(to_char(creation_date, 'MM'),'01', 1,0)) +
sum(DECODE(to_char(creation_date, 'MM'),'02', 1,0)) +
etc ....
sum(DECODE(to_char(creation_date, 'MM'),'12', 1,0)) ROW_TOTAL

from ....

If you want more help please post a resonable amount of test data AND exactly what your output based on the test data should look like.


In order to understand recursion, you must first understand recursion.
 
Upvote 0 Downvote
here is the sql again..
I need a total horizontally and then a full total vertically
select
sum(DECODE(to_char(creation_date, 'MM'),'01', 1,0)) JAN,
sum(DECODE(to_char(creation_date, 'MM'),'02', 1,0)) FEB,
sum(DECODE(to_char(creation_date, 'MM'),'03', 1,0)) MAR,
sum(DECODE(to_char(creation_date, 'MM'),'04', 1,0)) APR,
sum(DECODE(to_char(creation_date, 'MM'),'05', 1,0)) MAY,
sum(DECODE(to_char(creation_date, 'MM'),'06', 1,0)) JUN,
sum(DECODE(to_char(creation_date, 'MM'),'07', 1,0)) JUL,
sum(DECODE(to_char(creation_date, 'MM'),'08', 1,0)) AUG,
sum(DECODE(to_char(creation_date, 'MM'),'09', 1,0)) SEP,
sum(DECODE(to_char(creation_date, 'MM'),'10', 1,0)) OCT,
sum(DECODE(to_char(creation_date, 'MM'),'11', 1,0)) NOV,
sum(DECODE(to_char(creation_date, 'MM'),'12', 1,0)) DEC
from applsys.fnd_user
where creation_date > to_date('01/01/2010','MM-DD-YYYY')
GROUP BY creation_date
union
select
sum(DECODE(to_char(creation_date, 'MM'),'01', 1,0)) JAN,
sum(DECODE(to_char(creation_date, 'MM'),'02', 1,0)) FEB,
sum(DECODE(to_char(creation_date, 'MM'),'03', 1,0)) MAR,
sum(DECODE(to_char(creation_date, 'MM'),'04', 1,0)) APR,
sum(DECODE(to_char(creation_date, 'MM'),'05', 1,0)) MAY,
sum(DECODE(to_char(creation_date, 'MM'),'06', 1,0)) JUN,
sum(DECODE(to_char(creation_date, 'MM'),'07', 1,0)) JUL,
sum(DECODE(to_char(creation_date, 'MM'),'08', 1,0)) AUG,
sum(DECODE(to_char(creation_date, 'MM'),'09', 1,0)) SEP,
sum(DECODE(to_char(creation_date, 'MM'),'10', 1,0)) OCT,
sum(DECODE(to_char(creation_date, 'MM'),'11', 1,0)) NOV,
sum(DECODE(to_char(creation_date, 'MM'),'12', 1,0)) DEC
from applsys.fnd_user
where creation_date > to_date('01/01/2010','MM-DD-YYYY')
GROUP BY creation_date
order by 1;

so how can i add these with the example you gave:
select
sum(DECODE(to_char(creation_date, 'MM'),'01', 1,0)) +
sum(DECODE(to_char(creation_date, 'MM'),'02', 1,0)) +
etc ....
sum(DECODE(to_char(creation_date, 'MM'),'12', 1,0)) ROW_TOTAL
because your example has now left out the months i require..or am i misreading..this..SQL is not my forteit
 
Upvote 0 Downvote
Receiving a total horizontally....with this code
Code: 
select of_last_coy||''||of_last_div "COYDIV",
sum(DECODE(to_char(of_expiry_date, 'MM'),'01', 1,0)) JAN,
sum(DECODE(to_char(of_expiry_date, 'MM'),'02', 1,0)) FEB,
sum(DECODE(to_char(of_expiry_date, 'MM'),'03', 1,0)) MAR,
sum(DECODE(to_char(of_expiry_date, 'MM'),'04', 1,0)) APR,
sum(DECODE(to_char(of_expiry_date, 'MM'),'05', 1,0)) MAY,
sum(DECODE(to_char(of_expiry_date, 'MM'),'06', 1,0)) JUN,
sum(DECODE(to_char(of_expiry_date, 'MM'),'07', 1,0)) JUL,
sum(DECODE(to_char(of_expiry_date, 'MM'),'08', 1,0)) AUG,
sum(DECODE(to_char(of_expiry_date, 'MM'),'09', 1,0)) SEP,
sum(DECODE(to_char(of_expiry_date, 'MM'),'10', 1,0)) OCT,
sum(DECODE(to_char(of_expiry_date, 'MM'),'11', 1,0)) NOV,
sum(DECODE(to_char(of_expiry_date, 'MM'),'12', 1,0)) DEC,
count(*)                                             TOTAL
from mac.oprfile
where of_expiry_date > to_date('01-01-2010','DD-MM-YYYY')
GROUP BY of_last_coy, of_last_div
intersect 
select of_last_coy||''||of_last_div "COYDIV",
sum(DECODE(to_char(of_expiry_date, 'MM'),'01', 1,0)) JAN,
sum(DECODE(to_char(of_expiry_date, 'MM'),'02', 1,0)) FEB,
sum(DECODE(to_char(of_expiry_date, 'MM'),'03', 1,0)) MAR,
sum(DECODE(to_char(of_expiry_date, 'MM'),'04', 1,0)) APR,
sum(DECODE(to_char(of_expiry_date, 'MM'),'05', 1,0)) MAY,
sum(DECODE(to_char(of_expiry_date, 'MM'),'06', 1,0)) JUN,
sum(DECODE(to_char(of_expiry_date, 'MM'),'07', 1,0)) JUL,
sum(DECODE(to_char(of_expiry_date, 'MM'),'08', 1,0)) AUG,
sum(DECODE(to_char(of_expiry_date, 'MM'),'09', 1,0)) SEP,
sum(DECODE(to_char(of_expiry_date, 'MM'),'10', 1,0)) OCT,
sum(DECODE(to_char(of_expiry_date, 'MM'),'11', 1,0)) NOV,
sum(DECODE(to_char(of_expiry_date, 'MM'),'12', 1,0)) DEC,
count(*)                                             TOTAL
from mac.oprfile
where of_expiry_date > to_date('01-01-2010','MM-DD-YYYY')
GROUP BY of_last_coy, of_last_div
order by 1;

BUT I NEED a TOTAL vertically as well...for the month of jan,feb,mar,apr..any help will be highly appreciated
which w
 
Upvote 0 Downvote
Maybe I'm missing something, but why are you doing an INTERSECT of what appears to be exactly the same query ?



For Oracle-related work, contact me through Linked-In.
 
Upvote 0 Downvote
I was actually changed that from union all..in the hope to get the totals
 
Upvote 0 Downvote
But what are you doing it for ? Intersect gives you the results which are common to both queries. But, since both queries are the same, they will have the same result set and the will be virtually no difference between running this and running a single version of the query.

The only thing it will achieve is that it will remove any duplicate rows, which you could equally well achieve by doing "select distinct..."

For Oracle-related work, contact me through Linked-In.
 
Upvote 0 Downvote
thanks dagon
but i still need the totals per month..and a cumulative total...
And that is why..i need help
 
Upvote 0 Downvote
ok guys...
Code: 
select of_last_coy||''||of_last_div "COYDIV",
sum(DECODE(to_char(of_expiry_date, 'MM'),'01', 1,0)) JAN,
sum(DECODE(to_char(of_expiry_date, 'MM'),'02', 1,0)) FEB,
sum(DECODE(to_char(of_expiry_date, 'MM'),'03', 1,0)) MAR,
sum(DECODE(to_char(of_expiry_date, 'MM'),'04', 1,0)) APR,
sum(DECODE(to_char(of_expiry_date, 'MM'),'05', 1,0)) MAY,
sum(DECODE(to_char(of_expiry_date, 'MM'),'06', 1,0)) JUN,
sum(DECODE(to_char(of_expiry_date, 'MM'),'07', 1,0)) JUL,
sum(DECODE(to_char(of_expiry_date, 'MM'),'08', 1,0)) AUG,
sum(DECODE(to_char(of_expiry_date, 'MM'),'09', 1,0)) SEP,
sum(DECODE(to_char(of_expiry_date, 'MM'),'10', 1,0)) OCT,
sum(DECODE(to_char(of_expiry_date, 'MM'),'11', 1,0)) NOV,
sum(DECODE(to_char(of_expiry_date, 'MM'),'12', 1,0)) DEC,
count(*)                                             TOTAL
from mac.oprfile
where of_expiry_date > to_date('01-01-2010','DD-MM-YYYY')
GROUP BY of_last_coy, of_last_div
order by 1;
I have the row total..but i need the column totals as well..
any help will be highly appreciated
 
Upvote 0 Downvote
Well, as taupirho suggested, you could just add a second query which doesn't have the group by and join it to the first with a UNION ALL e.g.

Code: 
select * from
(
select of_last_coy||''||of_last_div "COYDIV",
sum(DECODE(to_char(of_expiry_date, 'MM'),'01', 1,0)) JAN,
sum(DECODE(to_char(of_expiry_date, 'MM'),'02', 1,0)) FEB,
sum(DECODE(to_char(of_expiry_date, 'MM'),'03', 1,0)) MAR,
sum(DECODE(to_char(of_expiry_date, 'MM'),'04', 1,0)) APR,
sum(DECODE(to_char(of_expiry_date, 'MM'),'05', 1,0)) MAY,
sum(DECODE(to_char(of_expiry_date, 'MM'),'06', 1,0)) JUN,
sum(DECODE(to_char(of_expiry_date, 'MM'),'07', 1,0)) JUL,
sum(DECODE(to_char(of_expiry_date, 'MM'),'08', 1,0)) AUG,
sum(DECODE(to_char(of_expiry_date, 'MM'),'09', 1,0)) SEP,
sum(DECODE(to_char(of_expiry_date, 'MM'),'10', 1,0)) OCT,
sum(DECODE(to_char(of_expiry_date, 'MM'),'11', 1,0)) NOV,
sum(DECODE(to_char(of_expiry_date, 'MM'),'12', 1,0)) DEC,
count(*)                                             TOTAL
from mac.oprfile
where of_expiry_date > to_date('01-01-2010','DD-MM-YYYY')
GROUP BY of_last_coy, of_last_div
UNION ALL
select 'TOTAL' "COYDIV",
sum(DECODE(to_char(of_expiry_date, 'MM'),'01', 1,0)) JAN,
sum(DECODE(to_char(of_expiry_date, 'MM'),'02', 1,0)) FEB,
sum(DECODE(to_char(of_expiry_date, 'MM'),'03', 1,0)) MAR,
sum(DECODE(to_char(of_expiry_date, 'MM'),'04', 1,0)) APR,
sum(DECODE(to_char(of_expiry_date, 'MM'),'05', 1,0)) MAY,
sum(DECODE(to_char(of_expiry_date, 'MM'),'06', 1,0)) JUN,
sum(DECODE(to_char(of_expiry_date, 'MM'),'07', 1,0)) JUL,
sum(DECODE(to_char(of_expiry_date, 'MM'),'08', 1,0)) AUG,
sum(DECODE(to_char(of_expiry_date, 'MM'),'09', 1,0)) SEP,
sum(DECODE(to_char(of_expiry_date, 'MM'),'10', 1,0)) OCT,
sum(DECODE(to_char(of_expiry_date, 'MM'),'11', 1,0)) NOV,
sum(DECODE(to_char(of_expiry_date, 'MM'),'12', 1,0)) DEC,
count(*)                                             TOTAL
from mac.oprfile
where of_expiry_date > to_date('01-01-2010','DD-MM-YYYY'))
order by decode(coydiv, 'TOTAL', 2,1)

For Oracle-related work, contact me through Linked-In.
 
Upvote 0 Downvote
thank you, thank you,thank you
just what i needed
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Sufi Shahzad
  • Locked
  • Question
Fingr printer reader digital personna 4500
Replies
0
Views
483
Sufi Shahzad
Sufi Shahzad
TheLazyPig
  • Locked
  • Question
Merge Into with multiple execution 1
Replies
1
Views
620
carp
carp
Livia8
  • Locked
  • Question
Question re: SELECT DISTINCT 1
Replies
6
Views
811
SkipVought
S
Livia8
  • Locked
  • Question
SQL SELECT - where date is null or greater than 2
Replies
9
Views
4K
carp
carp
sqlPower
  • Locked
  • Question
Help with Integer to Char to Datetime stamp
Replies
2
Views
668
SkipVought
S

Part and Inventory Search

Sponsor

Back
Top