current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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

share|improve this question
8  
Is there a reason why you are not using a DATE type for your time column? –  beny23 Aug 20 '12 at 12:17

2 Answers 2

up vote 0 down vote

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.

share|improve this answer
up vote 1 down vote

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.