Sum up a column in SQL

Question

I have four tables in my database which is Star Schema Design. Those tables are

1. Product_DM (Product_Id, Product_Name)
2. Shop_DM (Branch_Id, Branch_Name, Branch_State)
3. Date_DM (Date_Id as Date, Day, Month and Year). Day and Month and Year values are populated based on Date_Id.
4. Revenue_FT (Product_id, Branch_Id, Date_Id, Quantity)

What I want to have is I want to look at 5 BRANCH OR SHOP which sells most products last five years of Boxing day.

SELECT 
    RF.BRANCH_ID,
    SD.BRANCH_NAME,
    SD.BRANCH_STATE,
    PD.PRODUCT_NAME,
    SELF_RF.TOTAL,
    FORMAT ( DD.[date], 'd', 'en-US' ) AS 'Great Britain English Result'
FROM 
    PRODUCT_DM  AS PD,
    SHOP_DM     AS SD,
    DATE_DM     AS DD,
    REVENUE_FT  AS RF
JOIN 
    (SELECT BRANCH_ID, [date], SUM(quantity) AS TOTAL
     FROM REVENUE_FT
     GROUP BY BRANCH_ID, [date]) AS SELF_RF ON SELF_RF.BRANCH_ID = RF.BRANCH_ID 
                                            AND SELF_RF.[date] = RF.[date]
WHERE   
    RF.BRANCH_ID = SD.BRANCH_ID  
    AND SD.BRANCH_STATE = 'NSW'         
    AND RF.[date] = DD.[date]     
    AND DD.[day] = 26            
    AND DD.[month] = 12            
    AND DD.[year] BETWEEN 2012 AND 2018
ORDER BY 
    SELF_RF.TOTAL DESC;

This is the query I have and this is the result:

The problem is it is not summing up different products and different dates (for example 12/26/2013 and 12/26/2014 should also sum up). I know I am doing something wrong in my query but I needed a hand.

Answer 1

See an example below where you're able to get the top 5 branch / shops which have had the highest sales over the past 5 boxing days.

It's an example based off your listed table structure above.

;with GetBranchDetailsAndData
as
(
    --Joins the date table, sales, and shop details to get the branch details and quantity per transaction
    SELECT 
        c.Branch_Id,
        c.Branch_Name,
        c.Branch_State,
        a.Quantity
    FROM 
        Revenue_FT a
    INNER JOIN 
        DATE_DM  b
    ON 
        a.Date_Id = b.Date_Id
    INNER JOIN 
        Shop_DM c
    ON 
        c.Branch_Id = a.Branch_Id
    WHERE 
        [DAY] = 26 AND 
        [Month] = 12
        AND [Year] BETWEEN 2012 AND 2017 --Boxing days in 2012 - 2017
        --You could also filter on a specific state in the where clause

),
SUMDetailsAndData
as
(
    SELECT
        Branch_ID,
        Branch_Name,
        Branch_State, 
        SUM(Quantity) as [Quantity] --Sum quantity per branch
    FROM 
        GetBranchDetailsAndData
    GROUP BY
        Branch_ID,
        Branch_Name,
        Branch_State
), 
GetTop5
as
(
    SELECT
        Branch_ID,
        Branch_Name,
        Branch_State,
        Quantity,
        DENSE_RANK() OVER(ORDER BY Quantity DESC) as [QuantityOrder] --DENSE RANK to get the quantity order 
    FROM 
        SUMDetailsAndData
)
SELECT
    *
FROM
    GetTop5
WHERE 
    QuantityOrder <= 5 --Where the quantity order less or equal to 5. This will return multiple rows if there is multiple with the same number in the top 5. 
ORDER BY 
    QuantityOrder

Here is a snippet below which I used to generate testing data.

--Create tables
CREATE TABLE Product_DM (Product_Id bigint identity(1,1), Product_Name NVARCHAR(200))
CREATE TABLE Shop_DM (Branch_Id bigint identity(1,1), Branch_Name NVARCHAR(100), Branch_State NVARCHAR(100))
CREATE TABLE Date_DM (Date_Id bigint identity(1,1), [Day] int, [Month] int, [Year] int)
CREATE TABLE Revenue_FT (Product_id bigint, Branch_Id bigint, Date_Id bigint, Quantity bigint)
--Insert Data
INSERT INTO Product_DM (Product_Name) VALUES ('Test Product'),('Test Product2')
INSERT INTO Shop_DM (Branch_Name, Branch_State) VALUES 
('Branch1', 'State1'), ('Branch2', 'State1'), ('Branch3', 'State1'), ('Branch4', 'State1'), ('Branch5', 'State1'), 
('Branch6', 'State1'), ('Branch7', 'State1'), ('Branch8', 'State1'), ('Branch9', 'State1'), ('Branch10', 'State1')

DECLARE @DateStart date = '2010-01-01', @DateEnd date = '2018-01-01'
WHILE(@DateStart <= @DateEnd) 
BEGIN
    INSERT INTO DATE_DM ([day], [month], [year]) VALUES (DATEPART(dd, @datestart), DATEPART(MM, @datestart), DATEPART(YYYY, @datestart))
    SET @DateStart = DATEADD(dd, 1, @DateStart)
END

--Insert random product sales
DECLARE @MinProduct int = 1, @MaxProduct int = 2
DECLARE @MinBranch int = 1, @MaxBranch int = 10
DECLARE @MinDate int = 1, @MaxDate int = 2923
DECLARE @Startloop int = 1, @EndLoop int = 200000

WHILE @Startloop <= @EndLoop
BEGIN
    INSERT INTO Revenue_FT VALUES (
            ROUND(((@MaxProduct - @MinProduct) * RAND() + @MinProduct), 0), 
            ROUND(((@MaxBranch - @MinBranch) * RAND() + @MinBranch), 0), 
            ROUND(((@MaxDate - @MinDate) * RAND() + @MinDate), 0), 1)

    SET @Startloop = @Startloop + 1
END

Example output below: