-- тестовые данные
drop table if exists #Customers;
create table #Customers (
    [CustomerID] int,
    [CompanyName] varchar(255)
);

drop table if exists #Product;
create table #Product (
    [ProductID] int,
    [ProductName] varchar(255),
    [UnitPrice] money
);

drop table if exists #Orders;
create table #Orders (
    [OrderID] int,
    [CustomerID] int
);

drop table if exists #OrderDetailes;
create table #OrderDetailes (
    [OrderID] int,
    [ProductID] int,
    [Quntity] int
);

insert into #Customers
(CustomerID, CompanyName)
values
(1, 'Компания 1'),
(2, 'Компания 2'),
(3, 'Компания 3');

insert into #Product
(ProductID, ProductName, UnitPrice)
values
(1, 'Продукт 1', 12.50),
(2, 'Продукт 2', 10.00),
(3, 'Продукт 3', 99.95);

insert into #Orders
(OrderID, CustomerID)
values
(1, 1),
(2, 1),
(3, 1),
(4, 2),
(5, 2),
(6, 3);

insert into #OrderDetailes
(OrderID, ProductID, Quntity)
values
(1, 1, 5),
(1, 2, 3),
(1, 3, 2),
(2, 2, 7),
(2, 3, 4),
(3, 1, 10),
(4, 2, 4),
(4, 1, 6),
(5, 1, 7),
(5, 2, 5),
(3, 3, 4);

go
-- процедура
create or alter proc #get_customer_totals
@customer_id int
as
begin
    set nocount on;

    -- количество заказов, количество товара, общая стоимость
    select
        count(distinct o.OrderID) as orders_qty,
        isnull(sum(d.Quntity), 0) as total_qty,
        isnull(sum(d.Quntity * p.UnitPrice), 0) as total_price
    from #Orders AS o
    left join #OrderDetailes AS d on d.OrderID = o.OrderID
    left join #Product as p on p.ProductID = d.ProductID
    where o.CustomerID = @customer_id

    return;
end;
go
-- тест
exec #get_customer_totals
    @customer_id = 1;

exec #get_customer_totals
    @customer_id = 2;

exec #get_customer_totals
    @customer_id = 3;
go
-- убрать за собой
drop table if exists #Customers;
drop table if exists #Product;
drop table if exists #Orders;
drop table if exists #OrderDetailes;
drop proc if exists #get_customer_totals
go