getting "No column was specified for column 2 of 'd'" in sql server cte?

Question

I have this query, but its not working as it should,

with c as (select                 month(bookingdate) as duration,                 count(*) as totalbookings             from                 entbookings            group by month(bookingdate)           ),      d as (SELECT                 duration,                 sum(totalitems)             FROM                 [DrySoftBranch].[dbo].[mnthItemWiseTotalQty] ('1') AS BkdQty            group by duration           )  select      c.duration,      c.totalbookings,      d.bkdqty  from     c      inner join d      on c.duration = d.duration 

when I run this, I am getting

Msg 8155, Level 16, State 2, Line 1
No column was specified for column 2 of 'd'.

Can any one tell me what am I doing wrong?

Also, when I run this,

with c as (select                 month(bookingdate) as duration,                 count(*) as totalbookings             from                 entbookings            group by month(bookingdate)           ),      d as (select                 month(clothdeliverydate),                 SUM(CONVERT(INT, deliveredqty))             FROM                 barcodetable            where                 month(clothdeliverydate) is not null                group by month(clothdeliverydate)           )  select      c.duration,      c.totalbookings,      d.bkdqty  from     c      inner join d      on c.duration = d.duration 

I get

Msg 8155, Level 16, State 2, Line 1
No column was specified for column 1 of 'd'.
Msg 8155, Level 16, State 2, Line 1
No column was specified for column 2 of 'd'.

Answer 1

You just need to provide an alias for your aggregate columns in the CTE

d as (SELECT     duration,     sum(totalitems) as sumtotalitems FROM     [DrySoftBranch].[dbo].[mnthItemWiseTotalQty] ('1') AS BkdQty group by duration ) 

Answer 2

[edit]

I tried to rewrite your query, but even yours will work once you associate aliases to the aggregate columns in the query that defines 'd'.

---

I think you are looking for the following:

First one:

select      c.duration,      c.totalbookings,      d.bkdqty  from     (select                 month(bookingdate) as duration,                 count(*) as totalbookings             from                 entbookings            group by month(bookingdate)     ) AS c      inner join      (SELECT                 duration,                 sum(totalitems) 'bkdqty'            FROM                 [DrySoftBranch].[dbo].[mnthItemWiseTotalQty] ('1') AS BkdQty            group by duration     ) AS d      on c.duration = d.duration 

Second one:

select      c.duration,      c.totalbookings,      d.bkdqty  from     (select                 month(bookingdate) as duration,                 count(*) as totalbookings             from                 entbookings            group by month(bookingdate)     ) AS c      inner join      (select                 month(clothdeliverydate) 'clothdeliverydatemonth',                 SUM(CONVERT(INT, deliveredqty)) 'bkdqty'            FROM                 barcodetable            where                 month(clothdeliverydate) is not null                group by month(clothdeliverydate)     ) AS d      on c.duration = d.duration