MAX(column) with query

[james0816]

ok...one more interesting query if i may. I have the below query:

select c.a, c.b, c.c, c.d, c.e, c.f, c.g, c.h, c.i, c.j,

(case when p.ODATE > '31-DEC-2000' AND p.ODATE < '01-JAN-2002' then p.TCOST else 0 end) as COST00,
(case when p.ODATE > '31-DEC-2001' AND p.ODATE < '01-JAN-2003' then p.TCOST else 0 end) as COST01,
(case when p.ODATE > '31-DEC-2002' AND p.ODATE < '01-JAN-2004' then p.TCOST else 0 end) as COST02,
(case when p.ODATE > '31-DEC-2003' AND p.ODATE < '01-JAN-2005' then p.TCOST else 0 end) as COST03,
(case when p.ODATE > '31-DEC-2004' AND p.ODATE < '01-JAN-2006' then p.TCOST else 0 end) as COST04,
(case when p.ODATE > '31-DEC-2000' AND p.ODATE < '01-JAN-2006' then p.TCOST else 0 end) as COST05,


FROM TableA C, TableB P

where c.z='N' and c.a=p.a


I also want to add a column for MAX(p.odate). Everything I come up with so far, I get a message, "not a valid group by function".

Any help?

 

[r937]

do you understand grouping? have you done a lot of GROUP BY queries?

r937.com | rudy.ca

 

[james0816]

well....i thought i did. and yes...i do alot of grouping in other code that i write. mind you, i'm no professional coder...but i do get around.

 

[r937]

okay, let's say you want to add MAX(P.ODATE)

what groups do you want?


r937.com | rudy.ca

 

[james0816]

I am grouping by c.a then c.b

 

[r937]

what do you want to do with c.c, c.d, c.e, c.f, c.g, c.h, c.i, c.j, and all those COSTnn columns?

r937.com | rudy.ca

 

[james0816]

I have attempted to include them in the group by expression....it did not like it.

 

[r937]

i thought you said you only wanted c.a,c.b groups?

r937.com | rudy.ca

 

[james0816]

yes...that is correct. like i mentioned...everyway i coded it, i kept getting the not a valid group by expression. I'ved added columns...removed columns. maybe the brains just not firing on all cylinders right now.

 

[r937]

select C.a
     , C.b
     , min(C.c)
     , min(C.d)
     , min(C.e)
     , min(C.f)
     , min(C.g)
     , min(C.h)
     , min(C.i)
     , min(C.j)
     , sum(case when P.ODATE > '31-DEC-2000' AND P.ODATE < '01-JAN-2002' then P.TCOST else 0 end) as COST00
     , sum(case when P.ODATE > '31-DEC-2001' AND P.ODATE < '01-JAN-2003' then P.TCOST else 0 end) as COST01
     , sum(case when P.ODATE > '31-DEC-2002' AND P.ODATE < '01-JAN-2004' then P.TCOST else 0 end) as COST02
     , sum(case when P.ODATE > '31-DEC-2003' AND P.ODATE < '01-JAN-2005' then P.TCOST else 0 end) as COST03
     , sum(case when P.ODATE > '31-DEC-2004' AND P.ODATE < '01-JAN-2006' then P.TCOST else 0 end) as COST04
     , sum(case when P.ODATE > '31-DEC-2000' AND P.ODATE < '01-JAN-2006' then P.TCOST else 0 end) as COST05
  FROM TableA C
inner
  join TableB P
    on C.a = P.a
 where C.z = 'N'
group
    by C.a
     , C.b

r937.com | rudy.ca

 

[james0816]

hmmmmm....where does the max(p.odate) come in at?

 

[r937]

oh yeah, i forgot that!!

just add it to the SELECT

r937.com | rudy.ca