Just some SQL help! RSS

• • Reply
  

  howo5

  Member

  50 Points

  10 Posts

  Just some SQL help!

  Sep 23, 2003 03:52 PM|LINK

  I'm trying to use a group by statement on a subquery. How does one do this? example: select r2p.offer_pricing_gen_seq, sum(cu.contractual_consumption)/1000, r2p.ramp_hub, r2p.ramp_basis, r2p.spot_hub, r2p.spot_basis, r2p.risk_premium, (select r2.risk_premium from r2_price r2 where r2.product_id_gen_seq = 37 and r2.term_gen_seq = R2P.term_gen_seq and r2.offer_pricing_gen_Seq = R2P.offer_pricing_gen_seq and r2.pricing_parm_gen_seq = R2P.pricing_parm_gen_seq) from r2_price R2P, contractual_usage cu where r2p.offer_pricing_gen_seq = 9155 and r2p.accepted_ind = 'Y' and cu.offer_pricing_gen_seq = r2p.offer_pricing_gen_seq group by r2p.offer_pricing_gen_seq, r2p.ramp_hub, r2p.ramp_basis, r2p.spot_hub, r2p.spot_basis, r2p.risk_premium what do I name my subquery in the group by clause?
• • Reply
  

  joero4

  Member

  275 Points

  56 Posts

  Re: Just some SQL help!

  Sep 23, 2003 05:26 PM|LINK

  Try this...

  select r2p.offer_pricing_gen_seq, sum(cu.contractual_consumption)/1000,
  r2p.ramp_hub, r2p.ramp_basis, r2p.spot_hub, r2p.spot_basis, r2p.risk_premium, sub.risk_premium
  from r2_price R2P, contractual_usage cu,
  (select r2.risk_premium from r2_price r2 where r2.product_id_gen_seq = 37 and r2.term_gen_seq = R2P.term_gen_seq and r2.offer_pricing_gen_Seq = R2P.offer_pricing_gen_seq and r2.pricing_parm_gen_seq = R2P.pricing_parm_gen_seq) sub
  where r2p.offer_pricing_gen_seq = 9155 and r2p.accepted_ind = 'Y' and cu.offer_pricing_gen_seq = r2p.offer_pricing_gen_seq
  group by r2p.offer_pricing_gen_seq, r2p.ramp_hub, r2p.ramp_basis, r2p.spot_hub, r2p.spot_basis, r2p.risk_premium, sub.risk_premium

  I don't think it will receive the right data, but the query should run.
• • Reply
  

  howo5

  Member

  50 Points

  10 Posts

  Re: Just some SQL help!

  Sep 23, 2003 08:23 PM|LINK

  It doesn't allow me to join from my subquery to the main table when the subquery is in the from clause.
• • Reply
  

  joero4

  Member

  275 Points

  56 Posts

  Re: Just some SQL help!

  Sep 23, 2003 10:10 PM|LINK

  I'm not sure how to fix that, I would recomend tring to rewrite the query without a subquery. It may even be easier to simply break it up into two queries, or construct some sort of view from the sub query, that way you simply join on the view instead of using the subquery.