SQL71640: COUNT_BIG(a) is required

Question

SQL71640: COUNT_BIG(a) is required

aadm_GAdams 0

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

enter image description here

Olaf Helper 47,441 Reputation points

2023-06-12T08:13:07.4266667+00:00

Without seeing the SQL code no one can guess the cause. Provide also more details about your project.
LiHongMSFT-4306 31,571 Reputation points

2023-06-13T01:37:05.75+00:00

Hi @aadm_GAdams

As Dan Guzman answered below, are you using group by inside an indexed view.

Coby Lin 0

Here is the script:

CREATE MATERIALIZED VIEW [EDW_DM].[MVW_Fact_Transaction] WITH ( DISTRIBUTION = HASH (H_1Key)) AS  SELECT    [H_1Key],    [H_2Key],    [H_3Key],    [H_4Key],    [H_5Key],    [H_6Key],    [H_7Key],    [H_8Key],    [H_9Key],    [gDateId],    [H_TranCodeHashKey],    [L_DepartmentBusinessUnitHashKey],    SUM(CAST (ISNULL(Column_Name, 0) AS BIGINT)) AS Column_Name,    SUM(CAST (ISNULL(Column_Name, 0) AS MONEY)) AS Column_Name,    SUM(CAST (ISNULL(Column_Name, 0) AS MONEY)) AS Column_Name,    SUM(CAST (ISNULL(Column_Name, 0) AS BIGINT)) AS Column_Name,    SUM(CAST (ISNULL(Column_Name, 0) AS BIGINT)) AS Column_Name,    SUM(CAST (ISNULL(Column_Name, 0) AS BIGINT)) AS Column_Name,    SUM(CAST (ISNULL(Column_Name, 0) AS BIGINT)) AS Column_Name,    SUM(CAST (ISNULL(Column_Name, 0) AS BIGINT)) AS Column_Name,    SUM(CAST (ISNULL(Column_Name, 0) AS BIGINT)) AS Column_Name,    SUM(CAST (ISNULL(Column_Name, 0) AS BIGINT)) AS Column_Name,    SUM(CAST (ISNULL(Column_Name, 0) AS BIGINT)) AS Column_Name,    SUM(CAST (ISNULL(Column_Name, 0) AS BIGINT)) AS Column_Name,    SUM(CAST (ISNULL(Column_Name, 0) AS MONEY)) AS Column_Name,    SUM(CAST (ISNULL(Column_Name, 0) AS MONEY)) AS Column_Name,    SUM(CAST (ISNULL(Column_Name, 0) AS MONEY)) AS Column_Name,    SUM(CAST (ISNULL(Column_Name, 0) AS MONEY)) AS Column_Name,    SUM(CAST (ISNULL(Column_Name, 0) AS MONEY)) AS Column_Name,    SUM(CAST (ISNULL(Column_Name, 0) AS MONEY)) AS Column_Name,    SUM(CAST (ISNULL(Column_Name, 0) AS MONEY)) AS Column_Name,    SUM(CAST (ISNULL(Column_Name, 0) AS MONEY)) AS Column_Name,    SUM(CAST (ISNULL(Column_Name, 0) AS MONEY)) AS Column_Name,    SUM(CAST (ISNULL(Column_Name, 0) AS MONEY)) AS Column_Name,    SUM(CAST (ISNULL(Column_Name, 0) AS MONEY)) AS Column_Name,    SUM(CAST (ISNULL(Column_Name, 0) AS MONEY)) AS Column_Name,    SUM(CAST (ISNULL(Column_Name, 0) AS MONEY)) AS Column_Name,    SUM(CAST (ISNULL(Column_Name, 0) AS MONEY)) AS Column_Name,    SUM(CAST (ISNULL(Column_Name, 0) AS MONEY)) AS Column_Name,    SUM(CAST (ISNULL(Column_Name, 0) AS MONEY)) AS Column_Name,    SUM(CAST (ISNULL(Column_Name, 0) AS MONEY)) AS Column_Name,    SUM(CAST (ISNULL(Column_Name, 0) AS MONEY)) AS Column_Name,    SUM(CAST (ISNULL(Column_Name, 0) AS MONEY)) AS Column_Name,    COUNT_BIG(*) AS cb  FROM    [EDW_DM].[Fact_Transaction]  GROUP BY    [H_1Key],    [H_2Key],    [H_3Key],    [H_4Key],    [H_5Key],    [H_6Key],    [H_7Key],    [H_8Key],    [H_9Key],    gDateId,    [H_TranCodeHashKey],    [L_DepartmentBusinessUnitHashKey];

2 answers

Your answer

Olaf Helper 47,441 Reputation points

2023-06-12T08:13:07.4266667+00:00

Without seeing the SQL code no one can guess the cause. Provide also more details about your project.
LiHongMSFT-4306 31,571 Reputation points

2023-06-13T01:37:05.75+00:00

Hi @aadm_GAdams

As Dan Guzman answered below, are you using group by inside an indexed view.

Answer 1

Dan Guzman 9,406

This error will occur when you have an indexed view with a GROUP BY clause and the view SELECT clause does not include a COUNT_BIG(*). This is one of the requirements for indexed (materialized) views in SQL Server.

Ensure the view includes COUNT_BIG(), not COUNT(). For example:

CREATE VIEW dbo.ExampleView

WITH SCHEMABINDING

AS

SELECT

Column1

,COUNT_BIG(*) AS row_count

FROM dbo.ExampleTable

GROUP BY Column1;

Coby Lin 0 Reputation points

2023-06-13T02:03:19.7466667+00:00

Thanks.

The script of the materialized view is extracted from the Synapse database. And the materialized view itself is working fine in the database. COUNT_BIG() is included.
Dan Guzman 9,406 Reputation points

2023-06-14T10:16:17.26+00:00

@Coby Lin , add the code for the view to your question. Synapse has syntax differences compared to other SQL platforms so that might be the issue. What platform is the database project targeting?
Coby Lin 0 Reputation points

2023-06-14T20:47:37.3533333+00:00

Target platform:
Dan Guzman 9,406 Reputation points

2023-06-15T11:42:54.44+00:00

@Coby Lin , please edit your question to include the problem view definition and add the Azure Synapse Analytics tag. That will provide better visibility to experts that follow the tag.
Coby Lin 0 Reputation points

2023-06-15T21:52:45.1933333+00:00

@aadm_GAdams Could you update the tag please
Coby Lin 0 Reputation points

2023-06-15T22:56:29.9933333+00:00

Thx @Dan Guzman , added the script above.

Answer 2

Dan Guzman 9,406

I see from the view you provided that it contains several SUMs of various expressions like:

	, SUM(CAST (ISNULL(Column_Name1, 0) AS BIGINT)) AS Column_Name1
	, SUM(CAST (ISNULL(Column_Name2, 0) AS MONEY)) AS Column_Name2

The error message indicates that a COUNT_BIG() is also needed on the same expression as the SUM function. You can avoid the error by adding those to the SELECT list along with COUNT(BIG*) like below:

	, SUM(CAST (ISNULL(Column_Name1, 0) AS BIGINT)) AS Column_Name1
	, SUM(CAST (ISNULL(Column_Name2, 0) AS MONEY)) AS Column_Name2
	, COUNT_BIG(CAST(ISNULL(Column_Name1, 0) AS BIGINT)) AS Column_Name1_CountBig
	, COUNT_BIG(CAST(ISNULL(Column_Name2, 0) AS MONEY)) AS Column_Name2_CountBig
    , COUNT_BIG(*) AS CountBig

Coby Lin 0 Reputation points

2023-06-22T02:23:55.11+00:00

Have tried your solution, it is working now :). Thanks.
Dan Guzman 9,406 Reputation points

2023-07-01T10:56:20.27+00:00

@Coby Lin, happy to help. Please mark this as the answer to your question to help others with similar problems find the solution.

Share via

SQL71640: COUNT_BIG(a) is required

2 answers

Your answer