db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: VALUES Query Question
Date Fri, 12 Dec 2008 13:41:26 GMT
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

Mime
View raw message