CODEWITHSUNDEEP
sqldatabase
Rawan Al Maawali
Rawan Al Maawali

Reputation: 25

SQL group by item and group by month

I am using SQL Developer to query a database view. The view consist of the following columns: [DeviceName, DateOfCalculation, Value_of_Watt]

We have our devices set up in multiple houses to calculate their electricity Watt in a daily basis. So each deviceName is repeated multiple times in the view for each day it calculates.

I want to query this view by summing up the value of Watt of each month for each device. So each device's name is only repeated per month.year and not day.month.year.

As an example, this is our view now:

deviceName date wattValue
device1 1-may-21 60
device1 2-may-21 40
device1 3-may-21 45
....... ........ ..
device1 1-jun-21 45
....... ........ ..
device2 1-may-21 55
device2 2-may-21 57
device2 3-may-21 50
....... ........ ..
device2 1-jun-21 45
....... ........ ..

What we want is to sum up the Watt Value for each month for each device instead of each day, here the WattValue is summed up for that particular month:

deviceName date wattValue
device1 1-may-21 145
device1 1-Jun-21 100
....... ........ ..
....... ........ ..
device2 1-may-21 162
device2 1-Jun-21 190
....... ........ ..

Note: The calculaions are continous for years and years. The calculations in this question are random for the sake of the question.

Upvotes: 0

Views: 1201

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

SQL Developer is associated with Oracle. In Oracle, I would truncate the date:

select deviceName, trunc(date, 'MON') as yyyymm, sum(wattvalue)
from t
group by deviceName, trunc(date, 'MON')
order by yyyymm;

Upvotes: 1

Basto
Basto

Reputation: 216

You can try this :

SELECT deviceName, MONTH(date),YEAR(date), Sum(WattValue)
FROM YourTable
GROUP BY deviceName, MONTH(date),YEAR(date)

Upvotes: 3

Related Questions