Visual Studio Database Project MATERIALIZED VIEW Error SQL71640: COUNT_BIG(a) is required when using this tool to create a materialized view

Question

I have COUNT_BIG(**) in the script and I got the "SQL71640: COUNT_BIG(a) is required" error.*

Am I missing something here?

[

](https://i.stack.imgur.com/oeAob.png)

SQL71640: COUNT_BIG(a) is required when using this tool to create a materialized view that has SUM(a) in the SELECT list.

Answer 1

I had same issue in my project for Materialized view in Visual Studio 2022 database project. as I understand from error message visual studio needs to have separate count_big for each Summarized column. after I added extra count_big the errors gone.

SQL script with error in VS:
CREATE MATERIALIZED VIEW [dbo].[test]

WITH ( DISTRIBUTION = HASH (a))

AS
Select a, b, sum(c) as sum_c, sum(d) as sum_d, count_big(*) as cb

From mytable
group by a, b

when I compile above code I got this error
SQL71640: COUNT_BIG(a) is required when using this tool to create a materialized view that has SUM(a) in the SELECT list.

I changed the script to below code to resolve the error message
CREATE MATERIALIZED VIEW [dbo].[test]

WITH ( DISTRIBUTION = HASH (a))

AS
Select a, b, sum(c) as sum_c, sum(d) as sum_d, count_big(*) as cb, count_big(c) as cb2, count_big(d) as cb3

From mytable
group by a, b