CODEWITHSUNDEEP
sql-server
SaraniO
SaraniO

Reputation: 607

How to get last record based on a date with some other aggregate column in a SQL query

I know this question is already asked like 100 time and I reviewed all of them but I'm Kinda Stuck and had to asked for help

I have table like this:

hivenumber Visitdate   CombsNO  WaxNo  BeeBehave
------------------------------------------------
    1      2017-11-10    10       2        4
    2      2017-11-10    11       1        3
    3      2017-11-10    12       3        3
    1      2017-11-12    13       1        1
    3      2017-11-11    14       5        2

At first I want to aggregate it by HiveNumber

Select HiveNumber 
From tHivesDetails
Group BY HiveNumber

Then I want the last record of CombNo for each HiveNumber

Select Top(1) CombNo 
From `tHivesDetails` 
Order By VisitDate Desc

Then I need sum of Wax for each HiveNumber 

Select Sum(Wax) 
From `tHivesDetails` 
Group BY HiveNumber

and at the end I want average of BeeBehave

Select Avg(BeeBehave) 
From tHivesDetails
Group By HiveNumber

I don't know how to combine these queries to 1 and have one table with all I need in this case. I read most of same question but unfortunately couldn't figure it how do that.

I want a result like this:

hivenumber  Visitdate  CombsNO  WaxNo  BeeBehave
------------------------------------------------
    1      2017-11-12    13      Sum      avg
    2      2017-11-10    11      sum      avg
    3      2017-11-11    14      sum      avg

Upvotes: 1

Views: 2010

Answers (2)

Pat Jones
Pat Jones

Reputation: 896

Try this:

SELECT tA.HiveNumber, tA.WaxNoSum, tA.BeeBehaveSum, tB.CombsNoLatest
FROM (SELECT HiveNumber, SUM(WaxNo) AS WaxNoSum, AVG(BeeBehave) AS BeeBehaveSum  
      FROM tHivesDetails
      GROUP BY HiveNumber) AS tA LEFT JOIN (SELECT HiveNumber, MAX(CombsNO) AS CombsNoLatest
                                            FROM tHivesDetails
                                            GROUP BY HiveNumber) AS tB ON tA.HiveNumber = tB.HiveNumber

Upvotes: 1

Paul Maxwell
Paul Maxwell

Reputation: 35563

"Window Functions" to the rescue. You can use aggregate functions with an over clause to produce values on each row of a result. ROW_NUMBER() allows use of order by as well, do by ordering within each "partition" by the dates descending, the number 1 is given to "the most recent:" visit (per hive due to the partition).

select *
from (
       Select *
         , row_number() over(partition by HiveNumber order by VisitDate DESC) rn
         , sum(Wax) over(partition by HiveNumber) sum_wax
         , Avg(BeeBehave) over(partition by HiveNumber) avg_bb
       From tHivesDetails
      ) d
where rn = 1

Upvotes: 1

Related Questions