# Complex select

#### Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

•  Subject
• Author
• Posted on
Hi,

This is something I need to do with one select. Here is my select:

SELECT SUM(value1) FROM mytable WHERE rec_time BETWEEN '00:00:00' AND
'00:30:00' AND YEAR(rec_date) = YEAR(NOW()) GROUP BY MONTH(rec_date)

or

SELECT SUM(value1) FROM mytable WHERE rec_time BETWEEN '00:00:00' AND
'00:30:00' AND YEAR(rec_date) = YEAR(NOW()) GROUP BY LEFT(rec_date,7)

rec_time = time
rec_date = date

as you can see, I'm collecting summary of daily record around midnight and
building an monthly array like: 0,0,0,6.5,0,23.7,2,0,0,5.5,0,0
BUT! my function call requires a constant-length list. So if any months in
my selection range have no data, I need to fill the space with an empy
value. How can I do this? Perhaps with an helper table, but how?

Best regards,
-Ville Vahtera, Finland

## Re: Complex select

Ville Vahtera wrote:

Fill the helper table with every month value between the least rec_date
and the greatest rec_date.  Then join the helper table to your `mytable`
using an outer join:

SELECT SUM(...)
FROM mytable RIGHT OUTER JOIN monthTable
ON YEAR(mytable.rec_date) = YEAR(monthTable.rec_date)
AND MONTH(mytable.rec_date) = MONTH(monthTable.rec_date)
WHERE ...
GROUP BY YEAR(monthTable.rec_date), MONTH(monthTable.rec_date)

Regards,
Bill K.

## Re: Complex select

viestissä:di6ncb02kb4@enews2.newsguy.com...

thanx, I got it:

SELECT
IFNULL(SUM(mytable.value1), 0)
FROM one2twelve
LEFT JOIN mytable ON
MONTH(mytable.rec_date) = one2twelve.i AND
mytable.rec_time BETWEEN '00:00:00' AND '00:30:00' AND
YEAR(rec_date) = YEAR(NOW())
GROUP BY one2twelve.i;

Regards,
-Ville