I need to put amount '0' if the row exist but has no amount in my "where " conditions. [message #562096] |
Sat, 28 July 2012 01:23 |
|
arash_beterkun
Messages: 5 Registered: July 2012 Location: tehran
|
Junior Member |
|
|
I need to put amount '0' if the row exist but has no amount in my "where " conditions.
the orginal commad is :
select t.aaa, count (t.bbb), sum (t.ccc) from nrb t where t.vvv IN ('3','4','5','6','D','E','F') and t.ddd like '50%' and t.eee >= TO_DATE('2012/03/21','YYYY/MM/DD') and t.eee <= TO_DATE('2012/07/21','YYYY/MM/DD') group by t.aaa order by t.aaa
and the result is :
"result" tab in excel atached file.
i need this result:
"result 2" tab in excel atached file.
how can i have this ?
- all nessesary info are atached to the topic.
[Updated on: Sat, 28 July 2012 01:30] Report message to a moderator
|
|
|
|
|
|
|
|
|
Re: I need to put amount '0' if the row exist but has no amount in my "where " conditions. [message #562108 is a reply to message #562106] |
Sat, 28 July 2012 07:40 |
|
arash_beterkun
Messages: 5 Registered: July 2012 Location: tehran
|
Junior Member |
|
|
problem solved .
the answer is:
select t1.aaa, coalesce(t2.bbb_count, 0) bbb_count,
coalesce(t2.ccc_sum, 0) ccc_sum
from (
select distinct aaa
from nrb
) t1
left join (
select t.aaa, count (t.bbb) bbb_count, sum (t.ccc) ccc_sum
from nrb t
where t.vvv IN ('3','4','5','6','D','E','F')
and t.ddd like '50%'
and t.eee >= TO_DATE('2012/03/21','YYYY/MM/DD')
and t.eee <= TO_DATE('2012/07/21','YYYY/MM/DD')
group by t.aaa
) t2 on t1.aaa = t2.aaa
order by t1.aaa;
-> i have a new question:
this query will give me a table with 3 Columns. how can i make a table like excel sheet and loop the t.ddd line '50%' to t.ddd like '55%'. i mean i will have 15 Columns with same rows.
|
|
|
|