SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64) 
	Apr 11 2022 16:24:07 
	Copyright (C) 2019 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



create table customer(id int, name varchar(100));

-- ein paar bedeutungslose Firmennamen
insert into customer values(1, 'Alphabet');
insert into customer values(2, 'Baphoment');
insert into customer values(3, 'Chameleon');
3 rows affected

create table sales (id int, cid int, amount money, year int);
-- wobei cid die Referenz zum Kunden darstellt

-- einige bedeutungslose Sales Zahlen
insert into sales values 
  (1,1,20.22, 2012),(2,1,33.33, 2013),(3,1,4.44, 2014),
  (5,1,220.22, 2012),(6,1,3.33, 2013);

insert into sales values 
  (7, 2, 920.22, 2012), (8, 2, 33.33, 2013),
  (9, 2, 20.22, 2012);

insert into sales values 
  (11,3,220.22, 2012), (12,3,33.33, 2013),
  (13,3,4.44, 2014),    (14,3, 1110, 2011),
  (15,3,220.22, 2012), (16,3,33.33, 2013),
  (19,3,2.22, 2012), (20,3,1.11, 2011);
16 rows affected

-- finde das beste sales jahr überhaupt
select year, sum(amount) as total
  from sales
 group by year
 order by 2 desc;


year
total


2012
1623.5400

2011
1111.1100

2013
136.6500

2014
8.8800


-- sales Jahr Summe pro Kunde
select cid, year, sum(amount) as total
  from sales
 group by cid,year
 order by 1,3 desc;


cid
year
total


1
2012
240.4400

1
2013
36.6600

1
2014
4.4400

2
2012
940.4400

2
2013
33.3300

3
2011
1111.1100

3
2012
442.6600

3
2013
66.6600

3
2014
4.4400


-- die summierten Jahre je Kunde gejoint mit ihrem eigenen Maximum
with cyt as (
  -- sales Jahre pro Kunde summiert
  select cid, year, sum(amount) as totalsales
    from sales
   group by cid,year
)
select c.cid, year, mts as max_total
 from cyt c join (
      -- das Maximum je Kunde
      select cid,max(totalsales) mts from cyt group by cid) cmt  --customer  total
   on c.cid = cmt.cid and c.totalsales = cmt.mts;


cid
year
max_total


3
2011
1111.1100

2
2012
940.4400

1
2012
240.4400


-- jetzt das Gleiche mit Kundennamen dazu
-- die summierten Jahre je Kunde gejoint mit ihrem eigenen Maximum
with cyt as (
  -- bestes sales Jahr pro Kunde
  select cid, year, sum(amount) as totalsales
    from sales
   group by cid,year
)
select c.cid, cst.name, year as year_best, mts as max_total
 from cyt c 
  join (select cid,max(totalsales) mts from cyt group by cid) cmt  --customer  total
    on c.cid = cmt.cid and c.totalsales = cmt.mts
  join customer cst  -- plus Kunde, um den Namen zu bekommen
    on c.cid = cst.id;


cid
name
year_best
max_total


3
Chameleon
2011
1111.1100

2
Baphoment
2012
940.4400

1
Alphabet
2012
240.4400

cid	year	total
1	2012	240.4400
1	2013	36.6600
1	2014	4.4400
2	2012	940.4400
2	2013	33.3300
3	2011	1111.1100
3	2012	442.6600
3	2013	66.6600
3	2014	4.4400

cid	name	year_best	max_total
3	Chameleon	2011	1111.1100
2	Baphoment	2012	940.4400
1	Alphabet	2012	240.4400