How to make the two table LianZha statistical grouping-CodePudding

Gray two graph is a data source, a white figure is the query results,

Always couldn't get the right results, the following is my SQL,

For a correct results of SQL to run,

 
Select the um participant roupID, c.F irstName, c.L astName, SAN Antonio ob, e.Com panyName, c.E xternalID, Arthur c. mount the from (
The select b. irstName, b.L astName, b.J ob, count (*) as (count), Max (b.E xternalID) as ExternalID, min (Dr. _id) as c_id from 
(select ROW_NUMBER () over (order by (select (null))) as v_id, * from a) a 
Inner join 
(select ROW_NUMBER () over (order by (select (null))) as v_id, * from b) b 
On al-qeada roupID=b.G roupID group by b. irstName, b.L astName, b.J ob 
) as c 
Inner join 
(select ROW_NUMBER () over (order by (select (null))) as c_id, * from a) e 
On Arthur c. _id=_id of e.c. with our fabrication: 
The order by Count asc, CompanyName asc, FirstName desc

 

The CREATE TABLE [dbo]. [a] (
[the GroupID] [int] NULL, 
[CompanyName] [nvarchar] (50) NULL, 
) 

The CREATE TABLE [dbo] [b] (
[the GroupID] [int] NULL, 
[Year] [nvarchar] (50) NULL, 
[VenderID] [nvarchar] (50) NULL, 
[FirstName] [nvarchar] (50) NULL, 
[LastName] [nvarchar] (50) NULL, 
[the Job] [nvarchar] (50) NULL, 
[ExternalID] [nvarchar] (50) NULL, 
[Region] [nvarchar] (50) NULL, 
)

 
INSERT [dbo]. [a] ([GroupID], [CompanyName]) VALUES (35, N 'Shipping & amp; Co. ') 
INSERT [dbo]. [a] ([GroupID], [CompanyName]) VALUES (36, N 'Johnson and Sons') 
INSERT [dbo]. [a] ([GroupID], [CompanyName]) VALUES (39, N 'News Corp.') 
INSERT [dbo]. [a] ([GroupID], [CompanyName]) VALUES (40, N 'FireConsulting') 
INSERT [dbo]. [a] ([GroupID], [CompanyName]) VALUES (5, N 'WaterBus Enterprise') 
INSERT [dbo]. [a] ([GroupID], [CompanyName]) VALUES (6, N 'Alloy LLC) 
INSERT [dbo]. [a] ([GroupID], [CompanyName]) VALUES (27, N 'Machinx') 
GO 
INSERT [dbo] [b], [ID], [the GroupID], [Year], [VenderID], [FirstName], [LastName], [the Job], [ExternalID], [Region]) VALUES (1, 35, N '2018', N '102', N 'Daniel', N 'Knolle', N 'Manager', N '39765', N 'West') 
INSERT [dbo] [b], [ID], [the GroupID], [Year], [VenderID], [FirstName], [LastName], [the Job], [ExternalID], [Region]) VALUES (2, 35, N '2018', N '1988', N 'Arnold', N 'Sully', N 'Manager', N '48507', N 'West') 
INSERT [dbo] [b], [ID], [the GroupID], [Year], [VenderID], [FirstName], [LastName], [the Job], [ExternalID], [Region]) VALUES (3, 35, N '2018', N '1988', N 'Arnold', N 'Sully', N 'Manager', N '48507', N 'West') 
INSERT [dbo] [b], [ID], [the GroupID], [Year], [VenderID], [FirstName], [LastName], [the Job], [ExternalID], [Region]) VALUES (4, 36, N '2018', N '102', N 'Daniel', N 'Knolle', N 'Manager', N '8219', N 'West') 
INSERT [dbo] [b], [ID], [the GroupID], [Year], [VenderID], [FirstName], [LastName], [the Job], [ExternalID], [Region]) VALUES (5, 36, N '2018', N '1988', N 'Arnold', N 'Sully', N 'Manager', N '48507', N 'West') 
INSERT [dbo] [b], [ID], [the GroupID], [Year], [VenderID], [FirstName], [LastName], [the Job], [ExternalID], [Region]) VALUES (6, 39, N '2018', N '102', N 'Daniel', N 'Knolle', N 'Manager', N '8219', N 'West') 
INSERT [dbo] [b], [ID], [the GroupID], [Year], [VenderID], [FirstName], [LastName], [the Job], [ExternalID], [Region]) VALUES (7, 39, N '2018', N '102', N 'Daniel', N 'Knolle', N 'Manager', N '39765', N 'West') 
INSERT [dbo] [b], [ID], [the GroupID], [Year], [VenderID], [FirstName], [LastName], [the Job], [ExternalID], [Region]) VALUES (8, 39, N '2018', N '650', N 'Lisa', N 'Roberts', N' Manager ', N '1860', N 'West') 
INSERT [dbo] [b], [ID], [the GroupID], [Year], [VenderID], [FirstName], [LastName], [the Job], [ExternalID], [Region]) VALUES (' 2018 '9, 39, N, N' 650 ', N 'Lisa', N 'Roberts', N' Manager ', N '5397', N 'Central') 
INSERT [dbo] [b], [ID], [the GroupID], [Year], [VenderID], [FirstName], [LastName], [the Job], [ExternalID], [Region]) VALUES (10, 39, N '2018', N '1988', N 'Arnold', N 'Sully', N 'Manager', N '48507', N 'West') 
INSERT [dbo] [b], [ID], [the GroupID], [Year], [VenderID], [FirstName], [LastName], [the Job], [ExternalID], [Region]) VALUES (11, 39, N '2018', N '12', N 'Mary', N 'Dial', N 'Manager', N '1860', N 'East') 
INSERT [dbo] [b], [ID], [the GroupID], [Year], [VenderID], [FirstName], [LastName], [the Job], [ExternalID], [Region]) VALUES (12, 40, N '2019', N '102', N 'Daniel', N 'Knolle', N 'Manager', N '8219', N 'East') 
INSERT [dbo] [b], [ID], [the GroupID], [Year], [VenderID], [FirstName], [LastName], [the Job], [ExternalID], [Region]) VALUES (13, 40, N '2019', N '102', N 'Daniel', N 'Knolle', N 'Manager', N '39765', N 'West') 
INSERT [dbo] [b], [ID], [the GroupID], [Year], [VenderID], [FirstName], [LastName], [the Job], [ExternalID], [Region]) VALUES (14, 40, N '2019', N '650', N 'Lisa', N 'Roberts', N' Manager ', N '1860', N 'West') 
nullnullnullnullnullnullnullnullnullnull