CODEWITHSUNDEEP
sqlsql-server
Miku Hatsune
Miku Hatsune

Reputation: 31

Get last data recorded of the date and group it by month

tbl_totalMonth has id,time, date and kwh column.

I want to get the last recorded data of the months and group it per month so the result would be the name of the month and kwh.

the result should be something like this:

month    | kwh
------------
January  | 150
February | 400

the query I tried: (but it returns the max kwh not the last kwh recorded)

SELECT DATENAME(MONTH, a.date) as monthly, max(a.kwh) as kwh
                        from tbl_totalMonth a
                        WHERE date > = DATEADD(yy,DATEDIFF(yy,0, GETDATE() -1 ),0)
                        group by DATENAME(MONTH, a.date)

Upvotes: 1

Views: 82

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35603

I suspect you need something quite different:

select *
from (
      select *
          , row_number() over(partition by month(a.date), year(a.date) order by a.date DESC) as rn
      from tbl_totalMonth a
      WHERE date > = DATEADD(yy,DATEDIFF(yy,0, GETDATE() -1 ),0)
     ) d
where rn = 1

To get "the last kwh recorded (per month)" you need to use row_number() which - per month - will order the rows (descending) and give each one a row number. When that number is 1 you have "the most recent" row for that month, and you won't need group by at all.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133380

You could use group by and month 

select datename(month, date), sum(kwh)
from tbl_totalMonth 
where date =  (select max(date) from tbl_totalMonth )
group by datename(month, date)

if you need only the last row for each month then youn should use 

select datename(month, date), khw
from tbl_totalMonth a
inner join (
select max(date) as max_date  
from tbl_totalMonth 
group by month(date)) t on t.max_date = a.date

Upvotes: 0

Related Questions