You are viewing a plain text version of this content. The canonical link for it is here.

Posted to derby-user@db.apache.org by MassimoH <Ma...@gmail.com> on 2008/12/12 07:18:37 UTC

VALUES Query Question

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?

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

Re: VALUES Query Question

Posted by MassimoH <Ma...@gmail.com>.

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 <Ma...@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.

Re: VALUES Query Question

Posted by Knut Anders Hatlen <kh...@clustra.norway.sun.com>.

MassimoH <Ma...@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

Re: VALUES Query Question

Posted by Rick Hillegas <Ri...@Sun.COM>.

MassimoH wrote:
> 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?
>
> Thanks!
>   
Hi Massimo,

At first blush your query looks a lot like a grouped aggregate with an 
in list:

select salesMonth, sum( amount ) as sales
from sales
where salesMonth in
(
  date( '2008-01-01' ),
  date( '2008-02-01' ),
  date( '2008-03-01' ),
  date( '2008-04-01' ),
  date( '2008-05-01' ),
  date( '2008-06-01' ),
  date( '2008-07-01' )
)
group by salesMonth

Hope this helps,
-Rick