Common Table Expression, Sql Server

Common Table Expression

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

select @@version as 'sql server version'

CREATE TABLE Products (
    ProductID int,
    ProductName varchar(255),
    ProductCategory varchar(255),
    ProductPrice decimal(10)
    )
    
CREATE TABLE Sellers (
    SellerID int,
    SellerName varchar(255)
    )
    
CREATE TABLE Sales (
SaleID int,
ProductID_fk int,
SellerID_fk int
    )

INSERT INTO Products 
    (ProductID, ProductName, ProductCategory, ProductPrice)
VALUES
    (1, 'iPhone X',         'Technology',  1200),
    (2, 'Nike Vapor Shoes', 'Apparel',     180),
    (3, 'Ford Focus',       'Automobile', 18500),
    (4, 'Nissan Altima',    'Automobile', 18500),
    (5, 'Astros Cap',       'Apparel',      80),
    (6, 'Barcelona Jersey', 'Apparel',      90),
    (7, 'Samsung Galaxy',   'Technology',   850),
    (8, 'Apple Watch',      'Technology',   320)

INSERT INTO Sellers
    (SellerID, SellerName)
VALUES
    (1, 'Abi'),
    (2, 'Charlotte'),
    (3, 'Juliette')

INSERT INTO Sales 
    (SaleID, ProductID_fk, SellerID_fk)
VALUES
    (1,  1, 1),
    (2,  1, 1),
    (3,  2, 2),
    (4,  3, 2),
    (5,  3, 2),
    (6,  3, 2),
    (7,  5, 2),
    (8,  6, 3),
    (9,  6, 3),
    (10, 7, 3),
    (11, 9, 3)
    
-- Checking data per table  
-- SELECT * FROM Products
-- SELECT * FROM Sellers
-- SELECT * FROm Sales

-- Joining to show related data from all the tables
SELECT SellerName, ProductName, ProductPrice
FROM Sales
INNER JOIN Sellers ON SellerID = SellerID_fk
INNER JOIN Products ON ProductID = ProductID_fk

-- Showing Total amount of Sales by Seller and Total number of Sales
SELECT SellerName, SUM(ProductPrice) AS TotalSalesPerSeller, COUNT(ProductPrice) AS TotalNumberOfSales
FROM Sales
INNER JOIN Sellers ON SellerID = SellerID_fk
INNER JOIN Products ON ProductID = ProductID_fk
GROUP BY SellerName

-- Showing Total amount of Sales by Seller and Total nuber of Sales of only the Seller/s that have sold exactly 6 items
SELECT SellerName, SUM(ProductPrice) AS TotalSalesPerSeller
FROM Sales
INNER JOIN Sellers ON SellerID = SellerID_fk
INNER JOIN Products ON ProductID = ProductID_fk
GROUP BY SellerName
HAVING COUNT(ProductPrice) = 6

---- Total amount of Sale by the two Sellers that sold more items

-- Query to get total number of item sold by Seller
SELECT SellerID, COUNT(*) AS counter
INTO Occurrences
FROM Sales
INNER JOIN Sellers ON SellerID = SellerID_fk
GROUP BY SellerID;

-- CLE used to store the row index ordered in descending by number of item sold by seller
WITH OrderedList AS
(
    SELECT
        ROW_NUMBER() OVER(ORDER BY counter DESC) AS RowNumber,
        SellerID
    FROM Occurrences
)

-- Query that made use of the ordered list to get Total amount of Sales for the two Sellers with most number of item sold
SELECT 
    RowNumber, SellerName, SUM(ProductPrice) AS TotalSalesPerSeller
FROM Sales
INNER JOIN Sellers ON SellerID = SellerID_fk
INNER JOIN Products ON ProductID = ProductID_fk
INNER JOIN OrderedList o ON o.SellerID = SellerID_fk
WHERE RowNumber < 3
GROUP BY SellerName, RowNumber
ORDER BY RowNumber


run \| edit \| history \| help	0