how Get count of row and sum group by quarter of date, if another column doesnt exits a value in SQL Server

 

Questions


I have some sample data:

Date              Status       OfferNum        Amount
------------------------------------------------------
2016/10/30      -   1      -     2000      -  1000,00
2016/08/25      -   0      -     2000      -  1100,00
2016/07/12      -   0      -     2001      -  1200,00
2016/08/30      -   0      -     2001      -  1300,00
2016/07/12      -   1      -     2002      -  1400,00
2016/08/30      -   1      -     2002      -  1500,00
2016/08/30      -   1      -     2003      -  1600,00

I don’t want to count if one of offerNum status value has 1 and in the same quarter(if it has 1 but it isnt same quarter it has to be count). But I want to sum all of the amount(it isnt depends status column)

Here is the result that I want:

Quarter   Count                          TotalAmount
----------------------------------------------------
2016/Q3     2 (offerNum 2002 and 2003)      8100,00
2016/Q4     1 (offerNum 2000)               1000,00

Here is the sqlfiddle : http://sqlfiddle.com/#!6/eac9d

 

 

————————————————-

Answer

You can use a subquery to compute the status of each offer, then compute the final result aggregated by quarter. Notice that the GROUP BY year is important, otherwise result will contain data coming from the same quarter of the previous years.

--
-- Answer updated according to SQL Fiddle.
-- Check: http://sqlfiddle.com/#!6/709ff/9
--
WITH offers AS
(
    SELECT
        CONCAT(DATEPART(yy, date), '/Q', DATEPART(qq, date)) AS Quarter,
        offer,
        MAX(status) AS status,
        SUM(amount) AS TotalAmount
    FROM temp
    GROUP BY
        DATEPART(yy, date),
        DATEPART(qq, date),
        offer
)
SELECT
    Quarter,
    SUM(status) AS Count,
    SUM(TotalAmount) AS TotalAmount
FROM offers
GROUP BY Quarter

group-by,sql-server

Facebook Comments

Post a comment