RE: sql help needed

Can you group by a column name like that? I would think you would need
to do.

select sum(ilneprice) suma, case
    when ilninvdate between '02/01/2005' and '10/31/2005' then 1
    when ilninvdate between '11/01/2005' and '11/30/2005' then 2
 ...
    else 0
 end as period
 from...
 where..
 group by
case
    when ilninvdate between '02/01/2005' and '10/31/2005' then 1
    when ilninvdate between '11/01/2005' and '11/30/2005' then 2
 ...
    else 0
 end

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Douglas W. Palme
Sent: Wednesday, November 30, 2005 2:56 PM
To: Midrange Systems Technical Discussion
Subject: Re: sql help needed

Thanks Raul,
Just the medicine I needed :)


On Wed, 30 Nov 2005 16:50:07 -0300, Raul A. Jager W. wrote
> select sum(ilneprice) suma, case
>    when ilninvdate between '02/01/2005' and '10/31/2005' then 1
>    when ilninvdate between '11/01/2005' and '11/30/2005' then 2
> ...
>    else 0
> end as period
> from...
> where..
> group by period
> 
> You will get a row for each period.
>
________________________________________________________________________
__
> Douglas W. Palme wrote:
> 
> >Its been a few weeks since I last posted, and its been a over a week 
since I 
> >was even at work....actually I kind of missed a lot of you :) Trying
to 
get 
> >some rest and recoup time in a hospital bed just does not seem to
ever 
work.
> >
> >Alas, I am back, working on another stupid SQL problem.  If someone
could 
> >point me in the right direction on this I would appreciate it.
> >
> >I need to extract a sum of our line items for two separate date
ranges, 
IE: 
> >
> >select sum(ilneprice) as current from speclf where ilninvdate 
>'02/01/2005' 
> >and ilninvdate <='10/31/2005'
> >
> >That works just fine, but I also need to extract a sum for the same
field 
> >(ilneprice) for a different date range....is it possible to do this 
without 
> >having to run the query two separate times? Because I also need to 
extract 
> >some other information and group it by location codes and item
numbers....
> >
> >Any help would be appreciated.
> >
> >Douglas
> >
> >
> >If you bought it, it was hauled by a truck - somewhere, sometime.
> >
> >  
> >
> 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) 
> mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To 
> subscribe, unsubscribe, or change list options, visit: 
> http://lists.midrange.com/mailman/listinfo/midrange-l or email: 
> MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment 
> to review the archives at http://archive.midrange.com/midrange-l.


If you bought it, it was hauled by a truck - somewhere, sometime.