Home » SQL & PL/SQL » SQL & PL/SQL » query on a result of another query
query on a result of another query [message #267452] Thu, 13 September 2007 05:45 Go to next message
sonalshastry
Messages: 52
Registered: September 2007
Member
Hi


SELECT ent_id,ent_name, SUM(BROKERAGE1) Brokerage ,DT , ent_address_line_1,ent_address_line_2,ent_address_line_3,
ent_address_line_4,ent_address_line_5,ent_address_line_6,ent_address_line_7

FROM
(
select distinct e.ent_id, e.ent_name, to_CHAR(cm.cnm_date,'MONYYYY') DT,
sum(cm.cnm_brokerage_amt) Brokerage1,
e.ent_address_line_1,e.ent_address_line_2,e.ent_address_line_3,
e.ent_address_line_4,e.ent_address_line_5,e.ent_address_line_6,e.ent_address_line_7
from contract_note_master cm, entity_master e
where e.ent_id = cm.cnm_ent_id  
and cm.cnm_date >= '01-april-2007' and cm.cnm_date <= '31-Jun-2007'
and cm.cnm_sd_stage <> 'C'
and e.ent_status = 'E'
group by  e.ent_id,(to_CHAR(cm.cnm_date,'MONYYYY')), e.ent_name,e.ent_address_line_1,e.ent_address_line_2,
e.ent_address_line_3,e.ent_address_line_4,e.ent_address_line_5,e.ent_address_line_6,e.ent_address_line_7

Union all

select distinct e.ent_id, e.ent_name, to_CHAR(dcn_date,'MONYYYY')DT,
sum(dm.dcn_brokerage_amt) Brokerage1,
e.ent_address_line_1,e.ent_address_line_2,e.ent_address_line_3,
e.ent_address_line_4,e.ent_address_line_5,e.ent_address_line_6,e.ent_address_line_7
from dtm_contract_note_master dm, entity_master e
where  dm.dcn_ent_id = e.ent_id
and dcn_date >= '01-april-2007' and dcn_date <= '31-Jun-2007'
and dm.dcn_stage <> 'C'
and e.ent_status = 'E'
group by  e.ent_id,(to_CHAR(dcn_date,'MONYYYY')), e.ent_name,e.ent_address_line_1,e.ent_address_line_2,
e.ent_address_line_3,e.ent_address_line_4,e.ent_address_line_5,e.ent_address_line_6,e.ent_address_line_7
)

group by ent_id, ent_name, DT,ent_address_line_1, ent_address_line_2,ent_address_line_3,
ent_address_line_4,ent_address_line_5,ent_address_line_6, ent_address_line_7

having SUM(BROKERAGE1)>='2000' 



the above query gives me all the clients who gives me brokerage of 2000 or more below is the few records of result


   ENT_ID ENT_NAME  BROKERAGE	DT     ENT_ADDRESS_LINE_1	
1  2174	   SUNDAR    7631.36	JUN2007	  MODEL TOWN	
2  2174	   SUNDAR    7240.1	MAY2007	  MODEL TOWN	
3  2330	  NANDKISHOR 13899.0	JUN2007	  ALI CHAMBERS	
4  2330	  NANDKISHOR 13159.0	MAY2007	  ALI CHAMBERS	
5  2330	  NANDKISHOR 11299.0	APR2007	  ALI CHAMBERS	




but i want to add another condition that will gives me only those clients who gives me brokerage of 2000 or more for every month. like only recode of ENT_ID 2330 which is giving me brokerage in every month



please help me

sonal

[Updated on: Thu, 13 September 2007 07:14]

Report message to a moderator

Re: query on a result of another query [message #267463 is a reply to message #267452] Thu, 13 September 2007 06:41 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Looks like you need an analytical function, you can look it up in the SQL reference, it'll look something like:

select ...
,      sum(som_val) over (partition by id, month)
Re: query on a result of another query [message #267547 is a reply to message #267452] Thu, 13 September 2007 12:25 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
sonalshastry wrote on Thu, 13 September 2007 06:45

and cm.cnm_date >= '01-april-2007' and cm.cnm_date <= '31-Jun-2007'


and dcn_date >= '01-april-2007' and dcn_date <= '31-Jun-2007'



Invalid query. You cannot compare a DATE column to a character string. Please read Oracle documentation or search here or Google for the TO_DATE function.
Re: query on a result of another query [message #267569 is a reply to message #267547] Thu, 13 September 2007 13:25 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
BTW: the distincts seem unneccesary, since you're grouping/summing anyway.
Previous Topic: howto know a indexed column?
Next Topic: datafile max size
Goto Forum:
  


Current Time: Sat Dec 03 10:19:14 CST 2016

Total time taken to generate the page: 0.17491 seconds