Fylde record fra to forskellige selects

INSERT INTO [RepHotels] (Country, NumberOfDTP, NumberOfOther CreationDate)
select DestCountry, sum(noDTP), Sum(noOther ), getdate()
from (
SELECT
  DestCountry, Count (*) as noDTP, 0 as noOther,

FROM
  TMS.dbo.ORDERREP,
  TMS.dbo.Htlinf,
  TMS.dbo.Htlprice
WHERE
  ( TMS.dbo.ORDERREP.Htlno=TMS.dbo.Htlinf.Htlnr  )
  AND  ( TMS.dbo.Htlinf.Htlnr=TMS.dbo.Htlprice.Htlno  )
  AND  ( TMS.dbo.ORDERREP.Carcomp=TMS.dbo.Htlprice.Code  )
  AND  ( TMS.dbo.ORDERREP.Depdate between TMS.dbo.Htlprice.Fromdate and TMS.dbo.Htlprice.Todate  )
  AND  (
  TMS.dbo.ORDERREP.Invdate >= '09-01-2002'
  AND  TMS.dbo.ORDERREP.Art  =  'H'
  AND  TMS.dbo.ORDERREP.Destcountry = 'DK'
  AND  TMS.dbo.Htlinf.CustRef = 'DTP'
  )
GROUP BY
  DestCountry

union

SELECT
  DestCountry, 0 as noDTP, Count (*) as noOther)

FROM
  TMS.dbo.ORDERREP

WHERE
  (
  TMS.dbo.ORDERREP.Invdate >= '09-01-2002'
  AND  TMS.dbo.ORDERREP.Art  =  'H'
  AND  TMS.dbo.ORDERREP.Destcountry = 'DK'
  )
GROUP BY
  DestCountry
) x

Hmm.. Virker - næsten:

INSERT INTO [RepHotels] (Country, NumberOfDTP, NumberOfOther, CreationDate)
SELECT DestCountry, sum(noDTP), Sum(noOther ), getdate()
FROM (

SELECT
  DestCountry, Count (*) as noDTP, 0 as noOther

FROM
  TMS.dbo.ORDERREP,
  TMS.dbo.Htlinf,
  TMS.dbo.Htlprice
WHERE
  ( TMS.dbo.ORDERREP.Htlno=TMS.dbo.Htlinf.Htlnr  )
  AND  ( TMS.dbo.Htlinf.Htlnr=TMS.dbo.Htlprice.Htlno  )
  AND  ( TMS.dbo.ORDERREP.Carcomp=TMS.dbo.Htlprice.Code  )
  AND  ( TMS.dbo.ORDERREP.Depdate between TMS.dbo.Htlprice.Fromdate and TMS.dbo.Htlprice.Todate  )
  AND  (
  TMS.dbo.ORDERREP.Invdate >= '09-01-2002'
  AND  TMS.dbo.ORDERREP.Art  =  'H'
  AND  TMS.dbo.ORDERREP.Destcountry = 'DK'
  AND  TMS.dbo.Htlinf.CustRef = 'DTP'
  )
GROUP BY
  DestCountry

UNION

SELECT
  DestCountry, 0 as noDTP, Count (*) as noOther

FROM
  TMS.dbo.ORDERREP

WHERE
  (
  TMS.dbo.ORDERREP.Invdate >= '09-01-2002'
  AND  TMS.dbo.ORDERREP.Art  =  'H'
  AND  TMS.dbo.ORDERREP.Destcountry = 'DK'
  )
GROUP BY
  DestCountry
)


Giver

Server: Msg 170, Level 15, State 1, Line 42
Line 42: Incorrect syntax near ')'.

Hvor linje 42 er den sidste ')' nederst. Men det matcher jo korrekt nok starte parantesen lige før den første SELECT...

Du glemte det x, der står efter sidste parantes. Det SKAL med.

Hvad gør x'et?

Nu har jeg -

INSERT INTO [RepHotels] (Country, NumberOfDTP, NumberOfOther, CreationDate)
SELECT DestCountry, sum(noDTP), Sum(noOther ), getdate()
FROM (

SELECT
  DestCountry, Count (*) as noDTP, 0 as noOther

FROM
  TMS.dbo.ORDERREP,
  TMS.dbo.Htlinf,
  TMS.dbo.Htlprice
WHERE
  ( TMS.dbo.ORDERREP.Htlno=TMS.dbo.Htlinf.Htlnr  )
  AND  ( TMS.dbo.Htlinf.Htlnr=TMS.dbo.Htlprice.Htlno  )
  AND  ( TMS.dbo.ORDERREP.Carcomp=TMS.dbo.Htlprice.Code  )
  AND  ( TMS.dbo.ORDERREP.Depdate between TMS.dbo.Htlprice.Fromdate and TMS.dbo.Htlprice.Todate  )
  AND  (
  TMS.dbo.ORDERREP.Invdate >= '09-01-2002'
  AND  TMS.dbo.ORDERREP.Art  =  'H'
  AND  TMS.dbo.ORDERREP.Destcountry = 'DK'
  AND  TMS.dbo.Htlinf.CustRef = 'DTP'
  )
GROUP BY
  DestCountry

UNION

SELECT
  DestCountry, 0 as noDTP, Count (*) as noOther

FROM
  TMS.dbo.ORDERREP

WHERE
  (
  TMS.dbo.ORDERREP.Invdate >= '09-01-2002'
  AND  TMS.dbo.ORDERREP.Art  =  'H'
  AND  TMS.dbo.ORDERREP.Destcountry = 'DK'
  )
GROUP BY
  DestCountry
) x

og får

Server: Msg 8118, Level 16, State 1, Line 1
Column 'x.DestCountry' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

tilføj lige en ekstra GROUP BY DescCountry efter x

Sådan.. Tak.. Det X - hvilken betydning har det?

Det laver et alias på den select, der står inden i paranteserne. SQL Server kan ikke finde ud af syntaxen, hvis det alias ikke er der.