a query that sum the count of a row from multiple columns in SQL

I have to write a query that sum the count of a row from multiple columns

My code is like this, but total doesn’t work:

WITH 
NextPreviousItemAndTotal
AS
(
    SELECT LAG(Datetype, 1) OVER (PARTITION BY Fname ORDER BY Datetype ASC) AS PreviousItemDate,
            LAG(Fcount,1) OVER(PARTITION BY Fname ORDER BY Datetype) AS  PreviousItemCount,
            CurrentItemDate, CurrentItemCount,
            LEAD(Datetype, 1) OVER (PARTITION BY Fname ORDER BY Datetype ASC) AS NextItemDate, 
            LEAD(Fcount,1) OVER(PARTITION BY Fname ORDER BY Datetype) AS NextItemCount,
            SUM(Fcount) OVER(PARTITION BY Fname ORDER BY Datetype ) AS Total
    FROM FoodSara_tbl 
)
SELECT PreviousItemDate, PreviousItemCount, CurrentItemDate, CurrentItemCount,CurrentItemCount,NextItemCount, Total
FROM NextPreviousItemAndTotal

input:

|PreviousItemDate|PreviousItemCount|CurrentItemDate|CurrentItemCount|NextItemDate|NextItemCount|
 |----------------|-----------------|---------------|----------------|------------|-------------|
 |   Null         |    Null         |   2019/10/10  |      10        |2019/10/12  |  2          |
 |   2019/10/10   |     10          |2019/10/12     |    2           |2020/01/20  |  12         |
 |  2019/10/12    |  12             |2020/01/20     |   12           |Null        |  Null       |

output:

|PreviousItemDate|PreviousItemCount|CurrentItemDate|CurrentItemCount|NextItemDate|NextItemCount|Total|
 |----------------|-----------------|---------------|----------------|------------|-------------|-----|
 |   Null         |    Null         |   2019/10/10  |      10        |2019/10/12  |  2          |12   |
 |   2019/10/10   |     10          |2019/10/12     |    2           |2020/01/20  |  12         |24   |
 |  2019/10/12    |  12             |2020/01/20     |   8            |Null        |  Null       |20   |

Answer

Use coalesce as follows:

Coalesce(PreviousItemCount,0) 
+ Coalesce(currentItemCount,0)
+ Coalesce(nextItemCount,0) as total

Advertisement

Answer