I have created a Database called Business with the 2 tables as follows:
dbo.Orders
ID
OrderID
ItemName
Quantity
Price
SalesID
dbo.Sales
SalesID
FIrstName
LastName
OrderID
I need to create a stored procedure to show Total Sales of each sales, including SalesID, FirstName, LastName, Total Sales.
To create a stored procedure that shows the total sales for each salesperson, you can use the following script:
```sql
CREATE PROCEDURE GetTotalSales
AS
BEGIN
SELECT s.SalesID, s.FirstName, s.LastName, SUM(o.Quantity * o.Price) AS TotalSales
FROM dbo.Sales s
JOIN dbo.Orders o ON s.SalesID = o.SalesID
GROUP BY s.SalesID, s.FirstName, s.LastName
ORDER BY s.SalesID;
END
```
Let's break down the steps to understand how this stored procedure works:
1. The `CREATE PROCEDURE` statement is used to define a new stored procedure named `GetTotalSales`.
2. Inside the stored procedure, we use a select statement to retrieve the information we need. We select the `SalesID`, `FirstName`, `LastName`, and calculate the `TotalSales` by multiplying the `Quantity` and `Price` from the `dbo.Orders` table.
3. We join the `dbo.Sales` and `dbo.Orders` tables on the `SalesID` column, linking the two tables together.
4. We use the `GROUP BY` clause to group the result set by `SalesID`, `FirstName`, and `LastName`. This allows us to calculate the total sales for each salesperson.
5. Finally, we order the result set by `SalesID` in ascending order to have a clear view of each salesperson's total sales.
Once the stored procedure is created, you can execute it by using the following command:
```sql
EXEC GetTotalSales;
```
This will retrieve the total sales for each salesperson, showing the `SalesID`, `FirstName`, `LastName`, and `TotalSales`.