Error when modifying a View column with calculation

Question

I need to insert a calculation column in this view below, but the message appears

Msg 205, Level 16, State 1, Procedure Consolidadoestudobasesifix, Line 4 [Batch Start Line 9] All queries Combined using a UNION, INTERSECT or EXCEPT Operator must have an Equal number of Expressions in their target lists.

I know the mistake is in UNION ALL, but in the case did not want to insert more columns in the "percentage."

How can I resolve this issue?

Follows code:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[ConsolidadoBaseSifis] AS

with contabilidade as ( 

SELECT iUnidade,
COUNT(iUnidade) AS iQuantidade,
COUNT(vSimNao)  AS iSimNaoRespondido,
COUNT(vResp)  AS iJustificRespondidas
FROM  [dbo].[BaseSifis]
GROUP BY iUnidade
),

percentual as(

SELECT
(((iSimNaoRespondido+iJustificRespondidas)/iUnidade)*100) as iPorc
from contabilidade

)

select * FROM contabilidade 

Union ALL

select * FROM percentual 

In short, she’s like this:

And I’d like it to stay that way:

---

The original script is this:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[ConsolidadoBaseSifis] AS
    with contabilidade as ( 

    SELECT iUnidade,
    COUNT(iUnidade) AS iQuantidade,
    COUNT(vSimNao)  AS iSimNaoRespondido,
    COUNT(vResp)  AS iJustificRespondidas
    FROM  [dbo].[EstudoBaseSifis]
    GROUP BY iUnidade
    )

    select * FROM contabilidade 
GO

Answer 1

Try it this way:

ALTER VIEW ConsolidadoBaseSifis
AS
    WITH contabilidade  AS 
    ( 
        SELECT      iUnidade
                ,   COUNT(iUnidade) AS iQuantidade
                ,   COUNT(vSimNao)  AS iSimNaoRespondido
                ,   COUNT(vResp)    AS iJustificRespondidas
        FROM        BaseSifis
        GROUP BY    iUnidade
    ),  percentual      AS
    (
        SELECT  iUnidade
            ,   iQuantidade
            ,   iSimNaoRespondido
            ,   iJustificRespondidas
            ,   (((iSimNaoRespondido + iJustificRespondidas) / iUnidade) * 100) AS iPorc
        FROM    contabilidade
    )
    SELECT  *
    FROM    percentual
GO

If you really don’t want to put more columns, in addition to iPorc and of course, the iUnidade (otherwise you cannot connect the two tables), you can do so:

ALTER VIEW ConsolidadoBaseSifis
AS
    WITH contabilidade  AS 
    ( 
        SELECT      iUnidade
                ,   COUNT(iUnidade) AS iQuantidade
                ,   COUNT(vSimNao)  AS iSimNaoRespondido
                ,   COUNT(vResp)    AS iJustificRespondidas
        FROM        BaseSifis
        GROUP BY    iUnidade
    ),  percentual      AS
    (
        SELECT  iUnidade
            ,   (((iSimNaoRespondido + iJustificRespondidas) / iUnidade) * 100) AS iPorc
        FROM    contabilidade
    )
    SELECT      iUnidade
            ,   iQuantidade             = SUM(iQuantidade)
            ,   iSimNaoRespondido       = SUM(iSimNaoRespondido)
            ,   iJustificRespondidas    = SUM(iJustificRespondidas)
            ,   iPorc                   = SUM(iPorc)
    FROM        (
                    SELECT  iUnidade
                        ,   iQuantidade
                        ,   iSimNaoRespondido
                        ,   iJustificRespondidas
                        ,   iPorc = 0
                    FROM    contabilidade 
                    UNION ALL
                    SELECT  iUnidade
                        ,   iQuantidade             = 0
                        ,   iSimNaoRespondido       = 0
                        ,   iJustificRespondidas    = 0
                        ,   iPorc
                    FROM    percentual 
                ) X
    GROUP BY    iUnidade
GO

But it seems to me something too laborious and too little performant when what we want is basically the result of the table percentual.