CODEWITHSUNDEEP
sqloracle-databasevariablesloopsfor-loop
phips0r
phips0r

Reputation: 3

Oracle SQL SELECT in loop

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

Upvotes: 0

Views: 1411

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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.

Upvotes: 0

James_pic
James_pic

Reputation: 11

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

Upvotes: 1

Related Questions