# CTE para reswtar dos filas • ### Pregunta

• Hi,

Estoy queriendo hacer un CTE para calcular la resta de dos filas. Estas dos filas estan calculadas mediante :

```Select Level0_Account  , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010,
sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
FROM Aer_Lingus_data_Income
Where  Level0_Account ='Revenue'
Group by Level0_Account```

y la otra fila  mediante:

```UNION ALL
Select Name_Account   , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010,
sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
FROM Aer_Lingus_data_Income
Where  Name_Account ='Employee profit share' and Level0_Account ='Operating expenses'
Group by Name_Account```

el CTE la tengo en codigo de la siguiente forma:

```WITH sumasRevenue
AS (
SELECT  Name_Account, ID
, sum(Year_2006) AS Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010,
sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
FROM Aer_Lingus_data_Income
WHERE  Level0_Account = 'Revenue'
GROUP BY Name_Account, ID
WITH ROLLUP
)
, total
AS (
SELECT Y2006,Y2007 ,Y2008,Y2009 ,Y2010 ,Y2011 ,Y2012 ,Y2013
FROM sumasRevenue
WHERE Name_Account IS NULL
),
sumasOperatingExpensive
AS (
SELECT  Name_Account, ID
, sum(Year_2006) AS Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010,
sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
FROM Aer_Lingus_data_Income
WHERE  Level0_Account = 'Operating expenses'
GROUP BY Name_Account, ID
WITH ROLLUP
)
, total1
AS (
SELECT Y2006,Y2007 ,Y2008,Y2009 ,Y2010 ,Y2011 ,Y2012 ,Y2013
FROM sumasOperatingExpensive
WHERE Name_Account IS NULL
)

SELECT distinct  s.Name_Account,
(s.Y2006* 1.0 - t.Y2006) AS [V2006] ,(s.Y2007* 1.0 -T.Y2007 ) as [V2007]  , (s.Y2008* 1.0 -T.Y2008 ) as [V2008],(s.Y2009* 1.0 -T.Y2009 ) as [V2009],
(s.Y2010* 1.0 -T.Y2010 ) as [V2010],(s.Y2011* 1.0 -T.Y2011 ) as [V2011],(s.Y2012* 1.0 -T.Y2012 ) as [V2012],(s.Y2013* 1.0 -T.Y2013 ) as [V2013]
FROM sumasRevenue AS s , sumasOperatingExpensive AS t

UNION ALL

SELECT distinct COALESCE(s.Name_Account,'NON CURRENT ASSETS') AS   Name_Account  ,
sum (s.Y2006* 1.0 - t.Y2006) as  V2006,sum (s.Y2007* 1.0 - t.Y2007) as  V2007,sum (s.Y2008* 1.0- t.Y2008) as  V2008,sum (s.Y2009* 1.0- t.Y2009) as  V2009,sum (s.Y2010* 1.0- t.Y2010) as  V2010,
sum (s.Y2011* 1.0 - t.Y2011) as  V2011,sum (s.Y2012* 1.0- t.Y2012) as  V2012,sum (s.Y2013* 1.0- t.Y2013) as  V2013
FROM sumasRevenue as s , sumasOperatingExpensive as t
Where  s.Name_Account IS NOt NULL
GROUP BY s.Name_Account
WITH ROLLUP```

Pero estoy hacienda algo mal por que no funciona.

Gracias

jueves, 12 de febrero de 2015 8:58

### Respuestas

• lo que te falla son los group by y los select al poner ID y tener dos tablas casi siempre "s"y "t" tendras que poner en los select s.ID y en el group by igual s.ID

saludos.

viernes, 13 de febrero de 2015 12:27

### Todas las respuestas

• Y que es lo que no funciona? te da algun error o simplemente no trae los datos que esperas?

Sería bueno que agregues el script de creacion de tablas y datos de ejemplo en forma de INSERT para que podamos simular tu caso, ademas del resultado esperado en base a dichos datos.

jueves, 12 de febrero de 2015 14:15
• Hola Ronald,

Gracias por responder te dejo todo el scrip para tu analisis:

```USE Modelling
GO
--TABLE Aer_Lingus_Income_Statement

IF OBJECT_ID('Aer_Lingus_Income_Statement') IS NOT NULL
DROP TABLE Aer_Lingus_Income_Statement
GO
CREATE TABLE Aer_Lingus_Income_Statement
(
ID [nvarchar](255) NOT NULL,
Name_Account [nvarchar](255) NULL,
Company [nvarchar](255) NULL,
Level0_Account [nvarchar](255) NULL,
Level1_Account [nvarchar](255) NULL,
Level2_Account [nvarchar](255) NULL,
Level3_Account [nvarchar](255) NULL,
Level4_Account [nvarchar](255) NULL,
Level5_Account [nvarchar](255) NULL,
Level6_Account [nvarchar](255) NULL,
Level7_Account [nvarchar](255) NULL,
Level8_Account [nvarchar](255) NULL,
Year_2006 decimal (15,2)  null,
Year_2007 decimal (15,2)  null,
Year_2008 decimal (15,2)  null,
Year_2009 decimal (15,2)  null,
Year_2010 decimal (15,2)  null,
Year_2011 decimal (15,2)  null,
Year_2012 decimal (15,2)  null,
Year_2013 decimal (15,2)  null,
)
GO

ALTER TABLE Aer_Lingus_Income_Statement
ADD CONSTRAINT PK_Aer_Lingus_Income_Statement PRIMARY KEY (ID)
GO

INSERT INTO Aer_Lingus_Income_Statement
SELECT *
FROM  Aer_Lingus_data_Income

IF OBJECT_ID('Aer_Lingus_Income_Statement_Historic') IS NOT NULL
DROP VIEW Aer_Lingus_Income_Statement_Historic
GO
CREATE VIEW Aer_Lingus_Income_Statement_Historic
as

Select Level0_Account  , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010,
sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
FROM Aer_Lingus_data_Income
Where  Level0_Account ='Revenue'
Group by Level0_Account
UNION ALL
Select Name_Account  , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010,
sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
FROM Aer_Lingus_data_Income
Where Name_Account= 'Passenger revenue' and  Level0_Account ='Revenue'
Group by Name_Account
UNION ALL
Select Name_Account  , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010,
sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
FROM Aer_Lingus_data_Income
Where Name_Account= 'Ancillary revenue' and  Level0_Account ='Revenue'
Group by Name_Account
UNION ALL
Select Name_Account  , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010,
sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
FROM Aer_Lingus_data_Income
Where Name_Account= 'Other revenue' and  Level0_Account ='Revenue'
Group by Name_Account
UNION ALL
Select Name_Account  , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010,
sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
FROM Aer_Lingus_data_Income
Where Name_Account= 'Cargo revenue' and  Level0_Account ='Revenue'
Group by Name_Account
UNION ALL
Select Level0_Account  , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010,
sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
FROM Aer_Lingus_data_Income
Where  Level0_Account ='Operating expenses'
Group by Level0_Account
UNION ALL
Select Level1_Account  , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010,
sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
FROM Aer_Lingus_data_Income
Where  Level1_Account ='Staff costs' and Level0_Account ='Operating expenses'
Group by Level1_Account
UNION ALL
Select Level1_Account  , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010,
sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
FROM Aer_Lingus_data_Income
Where  Level1_Account ='Depreciation, amortisation and impairment' and Level0_Account ='Operating expenses'
Group by Level1_Account
UNION ALL
Select Name_Account  , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010,
sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
FROM Aer_Lingus_data_Income
Where  Name_Account ='Aircraft operating lease costs' and Level0_Account ='Operating expenses'
Group by Name_Account
UNION ALL
Select Name_Account  , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010,
sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
FROM Aer_Lingus_data_Income
Where  Name_Account ='Fuel and oil costs' and Level0_Account ='Operating expenses'
Group by Name_Account
UNION ALL
Select Name_Account  , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010,
sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
FROM Aer_Lingus_data_Income
Where  Name_Account ='Maintenance expenses' and Level0_Account ='Operating expenses'
Group by Name_Account
UNION ALL
Select Name_Account  , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010,
sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
FROM Aer_Lingus_data_Income
Where  Name_Account ='Airport charges' and Level0_Account ='Operating expenses'
Group by Name_Account
UNION ALL
Select Name_Account  , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010,
sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
FROM Aer_Lingus_data_Income
Where  Name_Account ='En-route charges' and Level0_Account ='Operating expenses'
Group by Name_Account
UNION ALL
Select Name_Account  , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010,
sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
FROM Aer_Lingus_data_Income
Where  Name_Account ='Distribution costs' and Level0_Account ='Operating expenses'
Group by Name_Account
UNION ALL
Select Name_Account  , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010,
sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
FROM Aer_Lingus_data_Income
Where  Name_Account ='Ground operations, catering and other operating costs' and Level0_Account ='Operating expenses'
Group by Name_Account
UNION ALL
Select Level1_Account   , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010,
sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
FROM Aer_Lingus_data_Income
Where  Level1_Account ='Other (gains)/losses - net' and Level0_Account ='Operating expenses'
Group by Level1_Account
UNION ALL
Select Name_Account   , sum(Year_2006) as Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010,
sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
FROM Aer_Lingus_data_Income
Where  Name_Account ='Employee profit share' and Level0_Account ='Operating expenses'
Group by Name_Account
GO

WITH sumasRevenue
AS (
SELECT  Name_Account, ID
, sum(Year_2006) AS Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010,
sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
FROM Aer_Lingus_data_Income
WHERE  Level0_Account = 'Revenue'
GROUP BY Name_Account, ID
WITH ROLLUP
)
, total
AS (
SELECT Y2006,Y2007 ,Y2008,Y2009 ,Y2010 ,Y2011 ,Y2012 ,Y2013
FROM sumasRevenue
WHERE Name_Account IS NULL
),
sumasOperatingExpensive
AS (
SELECT  Name_Account, ID
, sum(Year_2006) AS Y2006, sum (Year_2007) as Y2007, sum (Year_2008) as Y2008, sum (Year_2009) as Y2009, sum (Year_2010) as Y2010,
sum (Year_2011) as Y2011, sum (Year_2012) as Y2012, sum (Year_2013) as Y2013
FROM Aer_Lingus_data_Income
WHERE  Level0_Account = 'Operating expenses'
GROUP BY Name_Account, ID
WITH ROLLUP
)
, total1
AS (
SELECT Y2006,Y2007 ,Y2008,Y2009 ,Y2010 ,Y2011 ,Y2012 ,Y2013
FROM sumasOperatingExpensive
WHERE Name_Account IS NULL
)

SELECT distinct ID , s.Name_Account,
(s.Y2006* 1.0 - t.Y2006) AS [V2006] ,(s.Y2007* 1.0 -T.Y2007 ) as [V2007]  , (s.Y2008* 1.0 /T.Y2008 ) as [V2008],(s.Y2009* 1.0 /T.Y2009 ) as [V2009],
(s.Y2010* 1.0 /T.Y2010 ) as [V2010],(s.Y2011* 1.0 /T.Y2011 ) as [V2011],(s.Y2012* 1.0 /T.Y2012 ) as [V2012],(s.Y2013* 1.0 /T.Y2013 ) as [V2013]
FROM sumasRevenue AS s , sumasOperatingExpensive AS t
Where ID IS NOT NULL

UNION ALL

SELECT distinct ID,COALESCE(s.Name_Account,'NON CURRENT ASSETS') AS   Name_Account  ,
sum (s.Y2006* 1.0 / t.Y2006) as  V2006,sum (s.Y2007* 1.0 / t.Y2007) as  V2007,sum (s.Y2008* 1.0/ t.Y2008) as  V2008,sum (s.Y2009* 1.0/ t.Y2009) as  V2009,sum (s.Y2010* 1.0/ t.Y2010) as  V2010,
sum (s.Y2011* 1.0 / t.Y2011) as  V2011,sum (s.Y2012* 1.0/ t.Y2012) as  V2012,sum (s.Y2013* 1.0/ t.Y2013) as  V2013
FROM sumasRevenue as s , sumasOperatingExpensive as t
Where ID IS NULL and Name_Account IS NULL
GROUP BY Name_Account, ID
WITH ROLLUP

select *
from  Aer_Lingus_Income_Statement_Historic ```

Lo que intento es restar la fila de datos REVENUE y La fila de Datos Operating Expenses.

Error:

```(37 row(s) affected)
Msg 209, Level 16, State 1, Line 190
Ambiguous column name 'ID'.
Msg 209, Level 16, State 1, Line 186
Ambiguous column name 'ID'.```

• Editado jueves, 12 de febrero de 2015 15:28
jueves, 12 de febrero de 2015 15:27
• Lo que te aparece es un error, que en uno de tus selects, tienes 2 o mas tablas que tienen la columna ID y no SQL Server no sabe a cual quieres invocar, revisa la linea 190 y 186 de tu script, agregale el nombre de tabla o alias para identificar de forma independiente cada una de ellas..
jueves, 12 de febrero de 2015 17:59
• Hi Ronand,

I hope that you are well. Respecto a lo que me dices los datos son extraidos de la misma tabla, en este caso Aer_Lingus_data_Income, por eso creo que me da ese error.

Hay otra forma de resolverlo, Gracias

Un saludo

jueves, 12 de febrero de 2015 18:08
• lo que te falla son los group by y los select al poner ID y tener dos tablas casi siempre "s"y "t" tendras que poner en los select s.ID y en el group by igual s.ID

saludos.

viernes, 13 de febrero de 2015 12:27