Get data only from the last day of month but calculate average of a column from whole month

Question

I have a population table.

From this, I have to return only the population count of the last day of the month, which I have written a query that returns the below result:

Returns: Population count with the last day of the month

Code is below:

SELECT
         Country
       , [City Abbr]
       , [Area Code]
       , [Date]
FROM     [dbo].[Population]
WHERE    [Date] IN (
                       SELECT   MAX ([Date])
                       FROM     [dbo].[Population]
                       GROUP BY MONTH ([Date])
                              , YEAR ([Date])
                   )
ORDER BY [Area Code];

1. How to calculate the average for the whole month? I want to calculate the average temperature from the temperature column within the same population table and display it along with the population count from the last day of the month.

2. I have another table called Dead.dbo, where the mortality is recorded daily. I have to get the total dead for the month and display it with the same population table result.

The final result must be the population count on the last day of the month, the average temperature of the whole month and the Sum of dead (From the Dead table).

Your help and guidance will help me.

Regards, NewB

Answer 1

The easiest way is to do subqueries.. I didn't copy all of your data since i couldn't copy it to begin with, but I made an easy query with similar columns/values that you may be able to figure it out of.

Depending on your keys for the tables you'll have to expand the where clause for (probably) country, area code etc..

DECLARE @Population Table(
    [Date]          Date
  , [Population]    int
  , temperature     dec(5, 2)
)

DECLARE @DeathNumbers TABLE(
    [Date] Date
  , DeathNumber int
)

INSERT INTO @Population
SELECT       '2022-07-29', 1000, 32.8
UNION SELECT '2022-07-30', 980, 32.9
UNION SELECT '2022-07-31', 970, 33.9
UNION SELECT '2022-08-27', 800, 32.9
UNION SELECT '2022-08-28', 790, 32.9
UNION SELECT '2022-08-29', 785, 32.9
UNION SELECT '2022-08-30', 769, 32.9
UNION SELECT '2022-08-31', 750, 32.9
UNION SELECT '2022-09-28', 680, 32.9
UNION SELECT '2022-09-29', 675, 32.9
UNION SELECT '2022-09-30', 673, 32.9

INSERT INTO @DeathNumbers
SELECT '2022-07-29', 1
UNION SELECT '2022-07-30', 2
UNION SELECT '2022-08-15', 20
UNION SELECT '2022-08-18', 2
UNION SELECT '2022-08-22', 22
UNION SELECT '2022-09-01', 15
UNION SELECT '2022-08-29', 11

SELECT 
    MAX([Date]) as 'Date'
  , MAX(totalDeathCount) as 'Death Count this month'
  , AVG(temperature)    as 'Average Temperature'
FROM @Population m
OUTER APPLY(SELECT SUM(DeathNumber) as totalDeathCount 
  FROM @DeathNumbers t 
  WHERE MONTH(t.Date) = MONTH(m.Date)
) OA2
GROUP BY MONTH(m.[Date])