Oracle SQL SELECT in loop

Question

Hidiho,

I have a bunch of data from elecrticity meters. It's values are taken every hour for a whole year. Now i want to get them added and shown for every month. So that I get a table from Jan to Dec and its summed energyinformation. The Date-Type is "DD.MM.YYYY HH24".

Can I loop through the data and show every month in an extra table?

In Pseudo-Code it should look something like this:

For x in 1..12 loop
  SELECT energie, time FROM tbl_energie
  WHERE time  LIKE '%.0' + x + '.12%'     --(01.x.2012)
end loop;

Thanks for your help - Phips

Is there a reason why you are not using a DATE type for your time column? — beny23
– beny23, Commented Aug 20, 2012 at 12:17

James_pic · Accepted Answer · 2012-08-20 12:29:56Z

1

It's a little clunky, and it won't work too well in high performance code, but how about something like:

select sum(energie), time
from (
  select energie, to_char(time, 'MM.YYYY') as time
  from tbl_energie
)
group by time
order by time

answered Aug 20, 2012 at 12:29

James_pic

111 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2012-08-20 13:18:49Z

0

Since your time is stored as a string, you have two choices: either do string manipulation or convert to date/time.

Lets do this as string manipulation:

select substr(time, 4, 7), sum(energie)
from tbl_energie
group by substring(time, 4, 7)
order by 1

I would suggest that you also verify that you have meter readings on every hour, before believing the results.

answered Aug 20, 2012 at 13:18

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

Oracle SQL SELECT in loop

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related