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 <[email protected]> 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.
