How can I count data from a column being added in the same query?-CodePudding

I have a query setup for data verification that will join a few tables and auto-convert currencies from non-USD to USD.

SELECT c.Id, c.Currency
, cr.CPDealId, cr.UnderwriterId, cr.PlacedDate, cr.Fee, cr.FeeDue, cr.AllocatedFeeDue 
, f.Rate
, (CASE
    WHEN c.Currency <> 'USD'
        THEN (cr.FeeDue / f.Rate)
        ELSE (cr.FeeDue)        
END) AS 'TotalConverted'
FROM DB.DB.CPDeal c 
    INNER JOIN DB.DB.CPDealRate cr 
    ON (c.Id = cr.CPDealId)
    LEFT JOIN DB.DB.Fx f 
    ON (c.Currency = f.MainCurrency)
WHERE IssuerId = '1'
AND c.Date BETWEEN GETDATE()-90 AND GETDATE()
ORDER BY UnderwriterId DESC, PlacedDate DESC, Currency DESC;

The goal is to take the resulting 'Total Converted' column and SUM the data within for each row that has a matching value in the 'UnderwriterId' column.

(SELECT SUM('TotalConverted') AS 'UnderwriterTotal' FROM ??? GROUP BY UnderwriterId)

I was looking to add something along the lines of this when I realized I have no DB to point it to.

Can this be done? I feel like I've searched for ages, but I can't really figure out how to even search for a proper answer.

CodePudding user response：

Have you tried using subquery? I think this should work just fine

SELECT UnderwriterId, COUNT(TotalConverted) AS 'UnderwriterTotal' 
FROM
(SELECT c.Id, c.Currency
, cr.CPDealId, cr.UnderwriterId, cr.PlacedDate, cr.Fee, cr.FeeDue, cr.AllocatedFeeDue 
, f.Rate
, (CASE
    WHEN c.Currency <> 'USD'
        THEN (cr.FeeDue / f.Rate)
        ELSE (cr.FeeDue)        
END) AS TotalConverted
FROM DB.DB.CPDeal c 
    INNER JOIN DB.DB.CPDealRate cr 
    ON (c.Id = cr.CPDealId)
    LEFT JOIN DB.DB.Fx f 
    ON (c.Currency = f.MainCurrency)
WHERE IssuerId = '1'
AND c.Date BETWEEN GETDATE()-90 AND GETDATE()
ORDER BY UnderwriterId DESC, PlacedDate DESC, Currency DESC)
GROUP BY UnderwriterId
;

CodePudding user response：

I managed to solve this problem with the following window function:

SELECT t0.*
    , SUM(t0.TotalConverted) OVER(PARTITION BY t0.UnderwriterId) as 'UnderwriterTotal' 
FROM
(
SELECT c.Id, c.Currency
, cr.CPDealId, cr.UnderwriterId, cr.PlacedDate, cr.Fee, cr.AllocatedFeeDue AS 'TotalFeesDue'
, f.Rate
, (CASE
    WHEN c.Currency <> 'USD'
        THEN (cr.FeeDue / f.Rate)
        ELSE (cr.FeeDue)        
    END) AS TotalConverted
, u.Name 
FROM DB.DB.CPDeal c
    INNER JOIN DB.DB.CPDealRate cr
    ON (c.Id = cr.CPDealId)
    INNER JOIN DB.DB.Underwriter u  
    ON (u.Id = cr.UnderwriterId)
    LEFT JOIN DB.DB.Fx f
    ON (c.Currency = f.MainCurrency)
WHERE IssuerId = '1'
AND c.Date BETWEEN GETDATE()-90 AND GETDATE()
) t0
ORDER BY UnderwriterId DESC, PlacedDate DESC, Currency DESC;