Creating Materialized View in Azure Data Warehouse SQL error

Question

I'm getting the error:

Cannot create the clustered index 'SalesByTerritory' on view 'Sample.wwi.SalesByTerritory' because it does not aggregate results. Consider changing the view definition.

I'm trying to create a Materialized View in Azure SQL Data Warehouse (now Azure Synapse). I'm just tring to create a view with the sales table and getting the territory name and date from another table. Below is the code I'm using:

CREATE MATERIALIZED VIEW wwi.SalesByTerritory  
WITH (distribution = hash([Sale Key]))
AS
SELECT Sale.[Sale Key],
    Sale.[Description],
    Sale.[Quantity],
    Sale.[Profit],
    City.[Sales Territory],
    SaleDate.[Date],
    SaleDate.[Fiscal Month Label]
FROM wwi.fact_Sale Sale 
JOIN wwi.dimension_City City ON Sale.[City Key] = City.[City Key]
JOIN wwi.dimension_Date SaleDate ON Sale.[Invoice Date Key] = SaleDate.[Date]

Answer 1

That is probably because the Azure Synapse (DW) Materialized views can only work when there is some aggregation (probably to ensure uniqueness of some columns). This page talks about needing either of the below 2 conditions to be true:

1. The SELECT list contains an aggregate function.
2. GROUP BY is used in the Materialized view definition and all columns in GROUP BY are included in the SELECT list. Up to 32 columns can be used in the GROUP BY clause.

Answer 2

As from the official Microsoft documentation for Azure Synapse (formerly Azure SQL Data Warehouse), Aggregate functions are required in the SELECT list of the materialized view definition. Supported aggregations include MAX, MIN, AVG, COUNT, COUNT_BIG, SUM, VAR, STDEV.

Also, the SELECT list in the materialized view definition needs to meet at least one of these two criteria:

• The SELECT list contains an aggregate function.
• GROUP BY is used in the Materialized view definition and all columns in GROUP BY are included in the SELECT list. Up to 32 columns can be used in the GROUP BY clause.