db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From MassimoH <MassimoHei...@gmail.com>
Subject Re: VALUES Query Question
Date Fri, 12 Dec 2008 16:58:53 GMT

Thanks so much to both of you!

I actually wanted to do a cumulative YTD type query (each row has cumulative
data for all months before it), so it was hard to do that with just a simple
GROUP BY. This syntax works perfectly:

SELECT MONTHS.V
	, (SELECT SUM(SALES.QUANTITY)
	FROM SALES
	WHERE YEAR(BEGINDATE) = 2008
	AND MONTH(BEGINDATE) <= MONTHS.V) AS Sales
FROM (VALUES 1,2,3,4,5,6,7,8,9,10,11,12) AS MONTHS(V);



Knut Anders Hatlen-4 wrote:
> 
> MassimoH <MassimoHeitor@gmail.com> writes:
> 
>> I want to do a query like this:
>>
>> SELECT Months.????, (SELECT SUM(Amount) FROM Sales WHERE SaleMonth =
>> Months.????) AS Sales
>> FROM (VALUES '2008-01-01', '2008-02-01', '2008-03-01', '2008-04-01',
>> '2008-05-01', '2008-06-01', '2008-07-01') AS Months;
>>
>> However, when I use VALUES in a sub-query, I don't see how to reference
>> the
>> column. Basically, how can I get the above query to work?
> 
> Hi Massimo,
> 
> You can also rename columns with the AS clause. For instance, you can
> replace "AS Months" with "AS Months(x)", and then you can reference the
> column as Months.x.
> 
> -- 
> Knut Anders
> 
> 

-- 
View this message in context: http://www.nabble.com/VALUES-Query-Question-tp20970481p20979455.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.


Mime
View raw message