CODEWITHSUNDEEP
phpmysqllaravel
harunB10
harunB10

Reputation: 5207

Calculate the sum of columns for specified time interval

I have a table which consists of hours and the values of those hours. For example:

Hour    Value1    Value2
9       100        1
10      170        4
11       30       17
12      144       59
13        6        1
.
.
.
23       204       8

I need to calculate Value1 and Value2 for each column but for specified time period (interval).

For example: 

00-09h  Value1 = 100, Value2 =  1
09-11h  Value1 = 300, Value2 = 22
11-13h  Value1 = 180, Value2 = 77
13h-21h Value1 = ..., Value2 = ...

All I can think of is to create arrays for each interval, and then fill them with loop (check if value belongs to that interval, then push it to array).

But is there more convenient and efficient way to do this? With Query?

Upvotes: 2

Views: 212

Answers (2)

Sanzeeb Aryal
Sanzeeb Aryal

Reputation: 3266

Using sum() and whereBetween():

$model = Model::all();
$totalValue1=$model->whereBetween('hour',[9,11])->sum('value1');
$totalValue2=$model->whereBetween('hour',[9,11])->sum('value2');

$totalValue1=$model->whereBetween('hour',[11,13])->sum('value1');
$totalValue2=$model->whereBetween('hour',[11,13])->sum('value2');

$totalValue1=$model->whereBetween('hour',[13,21])->sum('value1');
$totalValue2=$model->whereBetween('hour',[13,21])->sum('value2');

Upvotes: 2

Paul Spiegel
Paul Spiegel

Reputation: 31792

You can create a derived table with the ranges on the fly and join it with your table:

select s.*, sum(t.Value1) as Value1, sum(t.Value2) as Value2
from (
    select 0  as start, 9  as end union all
    select 9  as start, 11 as end union all
    select 11 as start, 13 as end union all
    select 13 as start, 21 as end
) s
join your_table t on t.Hour between s.start and s.end
group by s.start, s.end

Upvotes: 2

Related Questions