I've got a problem with SQL query to make a chart in Python.

I want to make 12 rows for each month with highest value of deaths. Table look like this:

And the values are for each month from 2021.

I am at this moment

Table one:

Grudzień is December, and there are values for all of the months.

Expected output:

What I have tried

SELECT 
t.Year,t.MonthName,d.total_deaths
FROM dbo.Dim_time as t
INNER JOIN dbo.Deaths as d on t.ID_Date = d.ID_Date
where t.Year=2021
order by total_deaths DESC

And don't know how to select it.

CodePudding user response：

I did this:

SELECT t.MonthName, MAX(total_deaths) as total_deaths
 from dbo.Deaths as d 
inner join dbo.Dim_time as t on d.ID_Date = t.ID_Date 
where t.Year = 2021 
GROUP BY MonthName 
order by total_deaths asc

And it looks like this:

CodePudding user response：

I am guessing that you want from each year, from each month, the highest total_Deaths ?

If that is correct then you need a simple group by

declare @one table (year int, monthname varchar(50), total_deaths int)
insert into @one values (2021, 'december', 10), 
                        (2021, 'december', 20), 
                        (2021, 'december', 30),
                        (2021, 'november', 40), 
                        (2021, 'november', 60), 
                        (2021, 'november', 50),
                        (2020, 'december', 15), 
                        (2020, 'december', 25), 
                        (2020, 'december', 35)

select one.year, one.Monthname, max(one.total_deaths) as total_deaths 
from   @one one
group by one.year, one.Monthname

the result is

year    Monthname   total_deaths
2020    december    35
2021    december    30
2021    november    60