CODEWITHSUNDEEP
sqlsql-server
Adnan Yaseen
Adnan Yaseen

Reputation: 853

Don't want to use columns in the COUNT which are NULL

I have asked a question with the same query earlier but this time the question is different so I am posting another question. Below is the sql stored procedure query.

SELECT ROW_NUMBER() OVER (ORDER BY [PostedDate] Desc)AS RowNumber,[Products].[Id], [Name], [Description], [PostedDate], 
       ISNULL(AVG([Rating].[RatingValue]), 0) AverageRating, COUNT([Rating].[RatingValue]) RatingCount
       INTO #DealResults1
       FROM [Products]
       LEFT OUTER  JOIN [Rating] ON [Product].[Id] = [Rating].[ProductId]
       WHERE [City] = CASE WHEN @CityId IS NULL THEN [City] ELSE @CityId END 
       AND [Description] IS NOT NULL  
       Group by [Products].[Id], [Name], [Description], [PostedDate]
       ORDER BY [PostedDate] Desc

This is the query for 1 day. I have changed the table structure and now it is like this,

Id      Rating_Monday      Rating_Tuesday        Rating_Wednesday .......
 1            3.0                NULL                    NULL
 2            3.5                NULL                    NULL
 3            NULL               2.0                     NULL
 4            NULL               3.0                     NULL
 5            NULL               1.5                     NULL
 6            NULL               NULL                    1.0
 7            NULL               NULL                     2.5
 8            NULL               NULL                     4.5

On Monday, value for all other days will be Null. Now for Monday the rating selected is Rating_Monday. Everything works fine. In the above query there are two important parts which are,

ISNULL(AVG([Rating].[Rating_Monday]), 0) AverageRating
COUNT([Rating].[Rating_Monday]) RatingCount

Average is selected absolutely fine but RatingCount is wrong because it is selected according to row count and does not check for the Null value.

Upvotes: 0

Views: 107

Answers (4)

Past Tense
Past Tense

Reputation: 167

COUNT(expression) evaluates expression for each row in a group and returns the number of nonnull values.

You may look into using the OVER clause : COUNT(EmployeeID) OVER (PARTITION BY DepartmentID) AS EmployeesPerDept

See COUNT (Transact SLQ) for more information.

Upvotes: 2

Pரதீப்
Pரதீப்

Reputation: 93704

To count use condition aggragate logic.

Case statement will convert your NULL values to 0 on that put a SUM aggregate which will help you to count the rows only those are NOT NULL

SELECT Row_number()OVER (ORDER BY [PostedDate] DESC)        AS RowNumber,
       [Products].[Id],
       [Name],
       [Description],
       [PostedDate],
       Isnull(Avg([Rating].[RatingValue]), 0) AverageRating,
       Sum(CASE WHEN [Rating].[Rating_monday] IS NULL THEN 0 ELSE 1 END) RatingCount
INTO   #DealResults1
FROM   [Products]
       LEFT OUTER JOIN [Rating]
                    ON [Product].[Id] = [Rating].[ProductId]
WHERE  [City] = CASE WHEN @CityId IS NULL THEN [City] ELSE @CityId END
       AND [Description] IS NOT NULL
GROUP  BY [Products].[Id],
          [Name],
          [Description],
          [PostedDate]
ORDER  BY [PostedDate] DESC

Upvotes: 0

bowlturner
bowlturner

Reputation: 2016

Using a case can solve this.

SELECT ROW_NUMBER() OVER (ORDER BY [PostedDate] Desc)AS RowNumber,[Products].[Id], [Name],  [Description], [PostedDate], 
   ISNULL(AVG([Rating].[RatingValue]), 0) AverageRating, 
   SUM(case  when (Rating.RatingValue) is null then 0 else 1 END) RatingCount
   INTO #DealResults1
   FROM [Products]
   LEFT OUTER  JOIN [Rating] ON [Product].[Id] = [Rating].[ProductId]
   WHERE [City] = CASE WHEN @CityId IS NULL THEN [City] ELSE @CityId END 
   AND [Description] IS NOT NULL  
   Group by [Products].[Id], [Name], [Description], [PostedDate]
   ORDER BY [PostedDate] Desc

Upvotes: 1

simon at rcl
simon at rcl

Reputation: 7344

Try this for the count:

sum(case when [Rating].[Rating_monday] is null then 0 else 1 end)

Upvotes: 1

Related Questions