How can i amend this select to add totals 1

[grapes12]

I need to add totals per month, as well as a total amount for all months.
how can i amend this select to add totals.

WITH t1 AS
(SELECT CASE
WHEN mm = '01'
THEN tot
ELSE 0
END AS jan, CASE
WHEN mm = '02'
THEN tot
ELSE 0
END AS feb, CASE
WHEN mm = '03'
THEN tot
ELSE 0
END AS mar, CASE
WHEN mm = '04'
THEN tot
ELSE 0
END AS apr, CASE
WHEN mm = '05'
THEN tot
ELSE 0
END AS may, CASE
WHEN mm = '06'
THEN tot
ELSE 0
END AS jun, CASE
WHEN mm = '07'
THEN tot
ELSE 0
END AS jul, CASE
WHEN mm = '08'
THEN tot
ELSE 0
END AS aug, CASE
WHEN mm = '09'
THEN tot
ELSE 0
END AS sep, CASE
WHEN mm = '10'
THEN tot
ELSE 0
END AS oct, CASE
WHEN mm = '11'
THEN tot
ELSE 0
END AS nov, CASE
WHEN mm = '12'
THEN tot
ELSE 0
END AS DEC, CASE
WHEN mm IS NULL
THEN tot
ELSE 0
END AS total
FROM (SELECT TO_CHAR (CREATION_DATE, 'MM') mm, COUNT (1) AS tot
FROM applsys.fnd_user
WHERE creation_date > to_date('01-01-2010','DD-MM-YYYY')
GROUP BY ROLLUP (TO_CHAR (CREATION_DATE, 'MM'))
ORDER BY 1) a),
t2 AS
(SELECT SUM (jan) jan, SUM (feb) feb, SUM (mar) mar, SUM (apr) apr,
SUM (may) may, SUM (jun) jun, SUM (jul) jul, SUM (aug) aug,
SUM (sep) sep, SUM (oct) oct, SUM (nov) nov, SUM (DEC) DEC,
SUM (total) AS total
FROM t1)
SELECT *
FROM t1
UNION ALL
SELECT *
FROM t2

your help will be highly appreciated

 

[Dagon]

Isn't that what you are doing with the SUM(jan), SUM(feb) etc ?

For Oracle-related work, contact me through Linked-In.

 

[grapes12]

That is what i thought as well...but my senior wants row totals as well as column totals...

 

[carp]

ggoliath -
See if this will work for you:

WITH t1 AS
(SELECT CASE
WHEN mm = '01'
THEN tot
ELSE 0
END AS jan, 
CASE
WHEN mm = '02'
THEN tot
ELSE 0
END AS feb, 
CASE
WHEN mm = '03'
THEN tot
ELSE 0
END AS mar, 
CASE
WHEN mm = '04'
THEN tot
ELSE 0
END AS apr, 
CASE
WHEN mm = '05'
THEN tot
ELSE 0
END AS may, 
CASE
WHEN mm = '06'
THEN tot
ELSE 0
END AS jun, 
CASE
WHEN mm = '07'
THEN tot
ELSE 0
END AS jul, 
CASE
WHEN mm = '08'
THEN tot
ELSE 0
END AS aug, 
CASE
WHEN mm = '09'
THEN tot
ELSE 0
END AS sep, 
CASE
WHEN mm = '10'
THEN tot
ELSE 0
END AS oct, 
CASE
WHEN mm = '11'
THEN tot
ELSE 0
END AS nov, 
CASE
WHEN mm = '12'
THEN tot
ELSE 0
END AS DEC, 
CASE
WHEN mm IS NOT NULL
THEN gt
ELSE 0
END AS total
FROM (SELECT TO_CHAR (CREATION_DATE, 'MM') mm, COUNT (1) AS tot, count(1) AS gt
        FROM applsys.fnd_user
       WHERE creation_date > to_date('01-01-2010','DD-MM-YYYY')
       GROUP BY TO_CHAR (CREATION_DATE, 'MM')
      )
ORDER BY 1 desc, 2 desc, 3 desc, 4 desc, 5 desc, 6 desc,
         7 desc, 8 desc, 9 desc, 10 desc, 11 desc, 12 desc
), 
t2 AS (SELECT SUM (jan) jan, SUM (feb) feb, SUM (mar) mar, SUM (apr) apr,
       SUM (may) may, SUM (jun) jun, SUM (jul) jul, SUM (aug) aug,
       SUM (sep) sep, SUM (oct) oct, SUM (nov) nov, SUM (DEC) DEC,
       SUM (total) AS total
       FROM t1
      )
SELECT *
FROM t1
UNION ALL
SELECT * FROM t2;

 

[Dagon]

Try this:

WITH t1 AS
(SELECT CASE
WHEN mm = '01'
THEN tot
ELSE 0
END AS jan, CASE
WHEN mm = '02'
THEN tot
ELSE 0
END AS feb, CASE
WHEN mm = '03'
THEN tot
ELSE 0
END AS mar, CASE
WHEN mm = '04'
THEN tot
ELSE 0
END AS apr, CASE
WHEN mm = '05'
THEN tot
ELSE 0
END AS may, CASE
WHEN mm = '06'
THEN tot
ELSE 0
END AS jun, CASE
WHEN mm = '07'
THEN tot
ELSE 0
END AS jul, CASE
WHEN mm = '08'
THEN tot
ELSE 0
END AS aug, CASE
WHEN mm = '09'
THEN tot
ELSE 0
END AS sep, CASE
WHEN mm = '10'
THEN tot
ELSE 0
END AS oct, CASE
WHEN mm = '11'
THEN tot
ELSE 0
END AS nov, CASE
WHEN mm = '12'
THEN tot
ELSE 0
END AS DEC,  
mm,
tot AS total
FROM (SELECT TO_CHAR (CREATION_DATE, 'MM') mm, COUNT (1) AS tot
FROM fnd_user
WHERE creation_date > to_date('01-01-2010','DD-MM-YYYY')
GROUP BY ROLLUP (TO_CHAR (CREATION_DATE, 'MM'))
ORDER BY 1) a),
t2 AS
(SELECT SUM (jan) jan, SUM (feb) feb, SUM (mar) mar, SUM (apr) apr,
SUM (may) may, SUM (jun) jun, SUM (jul) jul, SUM (aug) aug,
SUM (sep) sep, SUM (oct) oct, SUM (nov) nov, SUM (DEC) DEC,
SUM (CASE WHEN mm IS NULL THEN total ELSE 0 END) AS total
FROM t1)
SELECT jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec, total
FROM t1
UNION ALL
SELECT jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec, total
FROM t2

For Oracle-related work, contact me through Linked-In.

 

[grapes12]

This works for me but not my senior...
he wants a total column added for evry month

JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC,TOTAL                                                       
----,----,----,----,----,----,----,----,----,----,----,----,-----                                                       
   2,   0,   0,   0,   0,   0,   0,   0,   0,   0,   0,   0,    2                                                       
   0,   0,  18,   0,   0,   0,   0,   0,   0,   0,   0,   0,   18                                                       
   0,   0,   0,   1,   0,   0,   0,   0,   0,   0,   0,   0,    1                                                       
   0,   0,   0,   0,   0,   0,   0,   0,   0,   0,   0,   0,   21                                                       
   2,   0,  18,   1,   0,   0,   0,   0,   0,   0,   0,   0,   21

any help will be appreciated

 

[grapes12]

here is the expected output..

    JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC,TOTAL

TOTAL 13,  16,  26,  13,   0,   0,   0,   0,   0,   0,   0,   0,   68

HOW CAN I ACHIEVE THIS?
Any help will be much appreciated.thanks

 

[grapes12]

Any help please guys!!!!

 

[Dagon]

I'm getting a bit lost as to what you want. At the moment you seem to have three types of output:

a) Rows for each month, which have the figure for that month and zeroes for all the other months, plus a total at the end which is obviously the same as the figure for the month.

b) A row with zeroes for each month and the total for all months at the end.

c) A row which has the totals for all the months and the grand total.

Are you saying you now just want the last row ?

For Oracle-related work, contact me through Linked-In.

 

[grapes12]

the last row should be the total..per month plus a grand total as in expected output..i posted earlier.

JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC,TOTAL

TOTAL 13,  16,  26,  13,   0,   0,   0,   0,   0,   0,   0,   0,   68

the row totaling totals for the month should have a column with total..as per example

 

[grapes12]

hi guys,

Found a solution. I'll post it.

SELECT
NVL(TO_CHAR(extract(YEAR FROM creation_date)),'TOTAL') CREATION_YEAR,
SUM(DECODE(extract (MONTH FROM creation_date),1,1,0)) JAN,
SUM(DECODE(extract (MONTH FROM creation_date),2,1,0)) FEB,
SUM(DECODE(extract (MONTH FROM creation_date),3,1,0)) MAR,
SUM(DECODE(extract (MONTH FROM creation_date),4,1,0)) APR,
SUM(DECODE(extract (MONTH FROM creation_date),5,1,0)) MAY,
SUM(DECODE(extract (MONTH FROM creation_date),6,1,0)) JUN,
SUM(DECODE(extract (MONTH FROM creation_date),7,1,0)) JUL,
SUM(DECODE(extract (MONTH FROM creation_date),8,1,0)) AUG,
SUM(DECODE(extract (MONTH FROM creation_date),9,1,0)) SEP,
SUM(DECODE(extract (MONTH FROM creation_date),10,1,0)) OCT,
SUM(DECODE(extract (MONTH FROM creation_date),11,1,0)) NOV,
SUM(DECODE(extract (MONTH FROM creation_date),12,1,0)) DEC,
SUM(1) total
FROM applsys.fnd_user
WHERE creation_date > to_date('01-01-2010','DD-MM-YYYY')
GROUP BY rollup(extract(YEAR FROM creation_date))

thanks for your help DAGON