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

calculated-columns, common-table-expression, group-by, sql, sql-server

Solution

[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

Problem

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'.

Original source