CODEWITHSUNDEEP
sqloracle-database
ashish
ashish

Reputation: 239

how to combine two columns in oracle

i have a query

select
    city,
    month,
    month_number,
    sum(totalcount) as totalcount,
    sum(total_value) total_value
from
(
    select
        city,
        to_char( testdate, 'Mon') month,
        to_number( to_char( testdate, 'mm' ) ) month_number,
        count( totalcount ) totalcount,
        sum( total_value ) total_value
    from 
        testtable
    group by
        city,
        testdate
)
group by
    city,
    month,
    month_number
order by
    1,3

which is giving result as image 1, how to modify this query which can give result as image 2?

image 1

image 2

Upvotes: 0

Views: 72

Answers (2)

user8406805
user8406805

Reputation:

Try the below SQL.

SELECT city, month,month_number
    sum(totalcount) as totalcount,
    sum(total_value) total_value
FROM   ( select
        city,
        to_char( testdate, 'Mon') month,
        to_number( to_char( testdate, 'mm' ) ) month_number,
        count( totalcount ) totalcount,
        sum( total_value ) total_value
    from 
        testtable
    group by
        city,
        testdate) x
GROUP BY ROLLUP (city, month,month_number)
ORDER BY 1,3

Upvotes: 0

Gaurav
Gaurav

Reputation: 1109

Use Oracle ROLLUP function in group by to achieve the desired result.

select
city,
month,
month_number,
sum(totalcount) as totalcount,
sum(total_value) total_value
from
(
    select
        city,
        to_char( testdate, 'Mon') month,
        to_number( to_char( testdate, 'mm' ) ) month_number,
        count( totalcount ) totalcount,
        sum( total_value ) total_value
    from 
        testtable
    group by
        city,
        testdate
)
group by
    city,
    ROLLUP (month,month_number)
order by
    1,3

Upvotes: 1

Related Questions