SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE [Customers]( [CustomerId] [int] NOT NULL IDENTITY(1,1), [AccountNumber] [nvarchar](15) NOT NULL DEFAULT(''), [AccountName] [nvarchar](200) NOT NULL, [SalesPersonName] [varchar](50) NOT NULL DEFAULT(''), [Active] [bit] NOT NULL DEFAULT(0), CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED ( [CustomerId] ASC ) ON [PRIMARY] ) ON [PRIMARY]; CREATE TABLE [Orders]( [OrderId] [int] NOT NULL IDENTITY(1,1), [OrderNumber] [nvarchar](20) NOT NULL, [OrderType] [nvarchar](15) NOT NULL, [CustomerId] [int] NOT NULL, [Address] [nvarchar](100) NULL, [City] [nvarchar](100) NULL, [State] [nvarchar](2) NULL, [ZipCode] [nvarchar](10) NULL, [BranchNumber] [int] NOT NULL, [SaleAmount] [decimal](16,2) NOT NULL, [OrderStatus] [nvarchar](25) NOT NULL, [OrderDate] [datetime2](7) NOT NULL, CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED ( [OrderId] ASC ) ON [PRIMARY] ) ON [Primary]; INSERT INTO [Customers] (AccountNumber, AccountName, SalesPersonName, Active) VALUES ('01402724', 'AAA Roofing', 'Desmond', '0'), ('03081191', 'BBB Builders', 'Frank', '0'), ('05727711', 'CCC Construction', 'Carla', '1'), ('04657357', 'DDD Doors & Windows', 'Carla', '0'), ('00577498', 'EEE Exteriors', 'Desmond', '1'), ('02929692', 'FFF Roof Repair', 'Carla', '1'), ('07737468', 'GGG Industries', 'Bruce', '1'), ('00426956', 'HHH Homes', 'Erin', '0'); INSERT INTO [Orders] (OrderNumber, OrderType, CustomerId, Address, City, State, ZipCode, BranchNumber, SaleAmount, OrderStatus, OrderDate) VALUES ('00040721587222540038', 'delivery', 6, '7775 Janae Land', 'Raquelburgh', 'TN', '29537', 586, 898.20, 'complete', '2021-06-11'), ('00014909031134685885', 'pickup', 2, NULL, NULL, NULL, NULL, 332, 3277.55, 'complete', '2021-08-01'), ('00015192395973008780', 'delivery', 3, '648 Cliff Ports','North Loree', 'MD', '51989-0498', 586, 4116.91, 'complete', '2021-09-08'), ('00074754364175377061', 'pickup', 5, NULL, NULL, NULL, NULL, 447, 7113.87, 'canceled', '2021-12-19'), ('00068406881304672506', 'delivery', 1, '746 Julio Glen', 'Lake Dion', 'AL', '23692-4053', 361, 3426.93, 'canceled', '2021-10-26'), ('00015806950923564623', 'pickup', 3, NULL, NULL, NULL, NULL, 332, 5365.44, 'complete', '2021-03-18'), ('00047569139102548661', 'pickup', 6, NULL, NULL, NULL, NULL, 586, 8465.73, 'out for delivery', '2021-10-05'), ('00061569994000421407', 'pickup', 2, NULL, NULL, NULL, NULL, 332, 778.73, 'complete', '2021-11-23'), ('00034270023258434443', 'pickup', 5, NULL, NULL, NULL, NULL, 447, 2307.48, 'complete', '2021-08-27'), ('00043717982953264002', 'delivery', 3, '8737 Jeana Crossing', 'Port Matt', 'NC', '95715', 332, 1810.24, 'complete', '2021-11-21'), ('00009075648541208189', 'delivery', 3, '430 Harris Meadow', 'Lake Frances', 'MO', '52204-7294', 361, 5360.04, 'submitted', '2021-01-12'), ('00028905157909731175', 'delivery', 7, '604 Reinger Centers', 'South Houstonbury', 'MA', '64466-1470', 160, 8343.46, 'confirmed', '2021-04-21'), ('00060005243189866618', 'delivery', 5, '2458 Abe Lodge', 'North Edwinfurt', 'ND', '36967', 586, 8304.01, 'in process', '2021-05-01'), ('00012628704063228951', 'delivery', 3, '83445 Rolanda Locks', 'Hesselfort', 'WA', '01982-9436', 160, 5615.52, 'confirmed', '2021-10-24'), ('00032818346091149090', 'pickup', 7, NULL, NULL, NULL, NULL, 586, 150.96, 'canceled', '2021-11-10'), ('00011510520629320735', 'delivery', 1, '301 Cole Meadows', 'Daughertyburgh', 'NM', '34007-2215', 508, 4163.43, 'canceled', '2021-07-08'), ('00057758436729768048', 'pickup', 6, NULL, NULL, NULL, NULL, 447, 5909.10, 'confirmed', '2021-11-17'), ('00059086121213610379', 'delivery', 5, '940 Haag Orchard', 'West Gerald', 'NM', '74957-8543', 361, 3977.12, 'complete', '2021-11-02'), ('00089505615542534639', 'delivery', 7, '79897 Jan Throughway', 'Muellerport', 'GA', '06555', 160, 2138.06, 'in process', '2021-07-15'), ('00087637425585390999', 'pickup', 8, NULL, NULL, NULL, NULL, 160, 1696.58, 'confirmed', '2021-02-26'), ('00085983462250263600', 'delivery', 5, '6489 Jennine Union', 'Edgardoland', 'NC', '75874-1090', 160, 6092.69, 'out for delivery', '2021-10-08'), ('00035097003761673719', 'pickup', 5, NULL, NULL, NULL, NULL, 332, 8319.21, 'complete', '2021-09-01'), ('00050419966716804793', 'pickup', 2, NULL, NULL, NULL, NULL, 586, 7707.06, 'complete', '2021-06-11'), ('00070750576178399997', 'pickup', 2, NULL, NULL, NULL, NULL, 508, 2321.17, 'out for delivery', '2021-07-08'), ('00031014816341600839', 'pickup', 7, NULL, NULL, NULL, NULL, 447, 4254.92, 'confirmed', '2021-09-19'), ('00033253077643674710', 'pickup', 8, NULL, NULL, NULL, NULL, 586, 2428.98, 'in process', '2021-10-14'), ('00038755241613871337', 'pickup', 3, NULL, NULL, NULL, NULL, 298, 8696.41, 'in process', '2021-07-03'), ('00094610206543008194', 'delivery', 1, '76714 Crona Forges', 'Wesshire', 'NH', '97702-9819', 298, 2395.12, 'complete', '2021-02-02'), ('00096956554038706105', 'delivery', 2, '27413 Cecil Neck', 'North Rosario', 'WA', '56683', 508, 7270.96, 'complete', '2021-05-21'), ('00032224668145955340', 'pickup', 5, NULL, NULL, NULL, NULL, 447, 5395.14, 'out for delivery', '2021-09-26'), ('00073095921500120913', 'pickup', 3, NULL, NULL, NULL, NULL, 361, 20.78, 'complete', '2021-07-12'), ('00071737571302210137', 'delivery', 8, '38780 William Gardens', 'Marcochester', 'DE', '51776', 447, 5635.55, 'confirmed', '2021-09-07'), ('00083051981502279345', 'delivery', 6, '94820 Eloise Key', 'Port Xaviermouth', 'VA', '34749', 508, 5864.47, 'in process', '2021-02-23'), ('00060921550246825363', 'pickup', 2, NULL, NULL, NULL, NULL, 447, 4548.36, 'canceled', '2021-04-21'), ('00071464996535141447', 'delivery', 1, '5664 Ruecker Ways', 'Yostville', 'CA', '87201', 447, 6703.14, 'out for delivery', '2021-08-14'), ('00026024812083033829', 'delivery', 2, '821 Johnie Grove', 'East Carisastad', 'VA', '40290', 586, 2088.74, 'submitted', '2021-06-09'), ('00087450970948705826', 'pickup', 1, NULL, NULL, NULL, NULL, 332, 8821.05, 'canceled', '2021-07-15'), ('00085921609139285626', 'pickup', 7, NULL, NULL, NULL, NULL, 447, 8855.80, 'complete', '2021-05-17'), ('00039474688508628795', 'delivery', 5, '8554 Merna Turnpike', 'West Manualville', 'WI', '18897-2522', 332, 4027.57, 'complete', '2021-07-13'), ('00022985940551594597', 'pickup', 6, NULL, NULL, NULL, NULL, 586, 8515.03, 'complete', '2021-01-10'), ('00039393954032726399', 'pickup', 5, NULL, NULL, NULL, NULL, 586, 5828.42, 'complete', '2021-02-07'), ('00085937216511306990', 'pickup', 3, NULL, NULL, NULL, NULL, 361, 3331.23, 'canceled', '2021-07-19'), ('00068373704016195437', 'delivery', 1, '377 Eldridge Meadow', 'East Jarrod', 'DE', '68947-6004', 298, 3991.04, 'confirmed', '2021-10-11'), ('00073397907267132404', 'delivery', 5, '23469 Avelina Rapids', 'East Lesleyton', 'WV', '94511-1968', 508, 6519.67, 'complete', '2021-07-20'), ('00050836024917667364', 'delivery', 1, '775 Linwood Loop', 'Lindgrenside', 'CA', '35814', 447, 5368.75, 'confirmed', '2021-12-08'), ('00073846444074155905', 'delivery', 2, '90208 Orlando Cape', 'North Tim', 'WI', '59088', 586, 1726.14, 'complete', '2021-09-18'), ('00054426453371141928', 'pickup', 2, NULL, NULL, NULL, NULL, 332, 5582.99, 'complete', '2021-01-23'), ('00062692005729213479', 'pickup', 8, NULL, NULL, NULL, NULL, 298, 2348.69, 'complete', '2021-04-03'), ('00089391161030174414', 'delivery', 6, '127 Kozey Stravenue', 'Port Shauna', 'VT', '67087-4647', 332, 8313.50, 'complete', '2021-06-02'); select SalesPersonName, sum(SaleAmount) from orders a join cutomers b a.customerID = b.customerID where orderstatus != 'canceled' group by SalesPersonName