State count and uf sql-CodePudding

I want to count the employees, by state and UF, but when I run my code it counts by department:

CREATE PROCEDURE SP_DATA_MART_CARGA 
AS 
IF NOT EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME = 'DATA_MART_CONTA_ESTADO') 
   BEGIN 
      CREATE TABLE DATA_MART_CONTA_ESTADO 
      (CD_MART INTIDENTITY(1,1) NOT NULL, 
       DEPTO VARCHAR(50) NULL, 
       ESTADO VARCHAR(30) NULL, 
       UF VARCHAR(2) NULL, 
       CONSTRAINT PK_DATA_MART_CONTA_ESTADO PRIMARY KEY(CD_MART)) 
   END 
ELSE 
   BEGIN 
     TRUNCATE TABLE DATA_MART_CONTA_ESTADO 
   END 
 
INSERT INTO DATA_MART_CONTA_ESTADO 
SELECT ESTADO,NM_ESTADO,UF 
FROM DWH  
 
EXECUTE SP_DATA_MART_CARGA 
 
SELECT * FROM DATA_MART_CONTA_ESTADO
 
CREATE VIEW FATO_CONTA 
AS 
SELECT DEPTO,COUNT(UF) AS 'CONTA' FROM DATA_MART_CONTA_ESTADO 
GROUP BY ESTADO

Can anyone help me?

CodePudding user response：

Your view throws a syntax error Msg 207, Level 16, State 1, Procedure FATO_CONTA, Line 3 Invalid column name 'deptno'.

amend to

CREATE VIEW FATO_CONTA 
AS 
SELECT estado,COUNT(UF) AS 'CONTA' FROM DATA_MART_CONTA_ESTADO 
GROUP BY ESTADO