Bdtjtk

If you just create "temp" database the following code can create and add sample data into the tables.

Query is to subtract TOTAL REFUNDS FROM TOTAL SALES which results in a new column named NET TOTAL.

Show: UserID, SiteID, Name, TOTAL Sales, Total Refunds, Net Total

image linked for sample data

Thanks

CREATE TABLE temp.dbo.Users 

(

    UserID INT,

    SiteID INT,

    Name VARCHAR(20)

)



CREATE TABLE temp.dbo.TRANSACTIONS

(

    TransactionID int,

    UserID int,

    SiteID int,

    TransactionType varchar(10),

    TransactionDate Date,

    Amount money

)



INSERT INTO temp.dbo.Users 

VALUES (1, 1, 'ARTHUR'), (2, 1, 'AARON'), (3, 2, 'BRETT')



INSERT INTO temp.dbo.TRANSACTIONS 

VALUES (4, 1, 1, 'SALE', GETDATE(), 120),

       (6, 1, 1, 'REFUND', GETDATE(), 120),

       (7, 2, 2, 'SALE', GETDATE(), 90),

       (9, 2, 1, 'SALE', GETDATE(), 30),

       (10, 2, 1, 'SALE', GETDATE(), 30),

       (11, 2, 1, 'SALE', GETDATE(), 30),

       (12, 2, 1, 'REFUND', GETDATE(), 30)

Query: This is my current query where I have tried to name it using WITH clause but no luck

SELECT 

    U.UserID, U.SiteID, U.Name,

    SUM(CASE WHEN T.TransactionType = 'SALE' THEN T.Amount ELSE 0 END) AS [TOTAL SALES],

    SUM(CASE WHEN T.TransactionType = 'REFUND' THEN T.Amount ELSE 0 END) AS [TOTAL REFUNDS]

FROM 

    temp.dbo.Users AS U

INNER JOIN 

    temp.dbo.Transactions AS T ON U.UserID = T.UserID AND U.SiteID = T.SiteID

GROUP BY 

    U.UserID, U.SiteID, U.Name

Welcome to Stack Overflow! Your question is very good, but it would be better if you provided an expected result set along with your sample data and work in progress.

There are a few approaches that will solve your problem:

SUM(CASE WHEN T.TransactionType = 'SALE' THEN T.Amount ELSE 0 END) - 

SUM(CASE WHEN T.TransactionType = 'REFUND' THEN T.Amount ELSE 0 END)

AS [NET TOTAL]

or

SUM(CASE 

   WHEN T.TransactionType = 'SALE' THEN T.Amount 

   WHEN T.TransactionType = 'REFUND' THEN -1 * T.Amount

   ELSE 0 

END) AS [NET TOTAL]

This might work

with c as(

SELECT 

    U.UserID,

    COUNT(U.UserID) OVER (PARTITION BY U.UserID) AS cnts,

    U.SiteID,

    U.Name,

    SUM(CASE WHEN T.TransactionType = 'SALE' THEN T.Amount ELSE 0 END) AS [TOTAL SALES],

    SUM(CASE WHEN T.TransactionType = 'REFUND' THEN T.Amount ELSE 0 END) AS [TOTAL REFUNDS]

    -- SUM(T.Amount) AS [TOTALS]

FROM 

    temp.dbo.Users AS U

INNER JOIN 

    temp.dbo.Transactions AS T ON U.UserID = T.UserID AND U.SiteID = T.SiteID

GROUP BY 

    U.UserID, U.SiteID, U.Name

)

select *

      ,[NET TOTAL] = [TOTAL SALES] - [TOTAL REFUNDS]

from c

also you can do this

SELECT 

    U.UserID,

    COUNT(U.UserID) OVER (PARTITION BY U.UserID) AS cnts,

    U.SiteID,

    U.Name,

    SUM(CASE WHEN T.TransactionType = 'SALE' THEN T.Amount ELSE 0 END) AS [TOTAL SALES],

    SUM(CASE WHEN T.TransactionType = 'REFUND' THEN T.Amount ELSE 0 END) AS [TOTAL REFUNDS],

    -- SUM(T.Amount) AS [TOTALS]

     SUM(CASE WHEN T.TransactionType = 'SALE' THEN T.Amount ELSE 0 END) 

    - SUM(CASE WHEN T.TransactionType = 'REFUND' THEN T.Amount ELSE 0 END) as [NET TOTAL]

FROM 

    temp.dbo.Users AS U

INNER JOIN 

    temp.dbo.Transactions AS T ON U.UserID = T.UserID AND U.SiteID = T.SiteID

GROUP BY 

    U.UserID, U.SiteID, U.Name