Question 113312 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How can I count data from a column being added in the same query?

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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;

  2. 0

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

    SELECT A.UnderwriterId, COUNT(A.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) A
GROUP BY A.UnderwriterId
;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search