CODEWITHSUNDEEP
sql
zizil
zizil

Reputation: 89

how to count net amount according to month

SELECT EXTRACT(month from doc_date) columnno, sum(net_amt_rm) sum_amt, max(doc_date) ddate
FROM POSM
WHERE EXTRACT (year from doc_date) = '2010'
GROUP BY EXTRACT(month from doc_date)

UNION //union no 2  
SELECT EXTRACT(month from doc_date) columnno, sum(net_amt_rm) sum_amt, max(doc_date) ddate  
FROM CIM  
WHERE EXTRACT(year from doc_date) = '2010'  
GROUP BY EXTRACT(month from doc_date)   

UNION  //union no3  
SELECT EXTRACT(month from doc_date) columnno, sum(net_amt_rm) sum_amt, max(doc_date) ddate  
FROM CAM  
WHERE EXTRACT(year from doc_date) = '2010'  
GROUP BY EXTRACT(month from doc_date)

the output for this query is like this:

columnno | sum amt | ddate  
3             6      01/march/2010  
7            250     29/july/2010 // output for union no2  
7           2617     02/july/2010 // output for union no3

but i want my output to be like this

columnno | sum amt | ddate  
3             6      march  
7            2867    july //output for union no 2 and 3 are sum together to get only one july

its means that total the amount in one month only

Upvotes: 1

Views: 147

Answers (1)

Bharat
Bharat

Reputation: 6866

try this one

select columnno , sum(sum_amt), EXTRACT(month from ddate) ddate from 
<your query> 
group by columnno , (EXTRACT(month from ddate))

Upvotes: 2

Related Questions