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

User · Answer

evidently  as stated in the parser response  a column name is needed for both cases  In either versions the columns of  d  are not named   in case 1  your column 2 of d is sum totalitems  which is not named  duration will retain the name  duration   in case 2  both month clothdeliverydate  and SUM CONVERT INT  deliveredqty   have to be named

User · Answer

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  ROUND AVG CAST column name AS FLOAT    2  as column name

User · Answer

Because you are creatin a table expression  you have to specify the structure of that table  you can achive this on two way   1  In the select you can use the original columnnames  as in your first example   but with aggregates you have to use an alias  also in conflicting names    Like   sum totalitems  as bkdqty   2  You need to specify the column names rigth after the name of the talbe  and then you just have to take care that the count of the names should mach the number of coulms was selected in the query  Like   d  duration  bkdqty   AS  Select          With the second solution both of your query will work

User · Answer

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

User · Answer

Quite an intuitive error message - just need to give the columns in d names  Change to either this  d as       select                        duration    month clothdeliverydate                          bkdqty    SUM CONVERT INT  deliveredqty                  FROM                       barcodetable               where                       month clothdeliverydate  is not null                   group by month clothdeliverydate                   Or you can explicitly declare the fields in the definition of the cte   d   duration    bkdqty   as       select                       month clothdeliverydate                         SUM CONVERT INT  deliveredqty                  FROM                       barcodetable               where                       month clothdeliverydate  is not null                   group by month clothdeliverydate

User · Answer

I had a similar query and a similar issue   SELECT       FROM     Users ru     LEFT OUTER JOIN                SELECT ru1 UserID  COUNT            FROM Referral r         LEFT OUTER JOIN Users ru1 ON r ReferredUserId   ru1 UserID         GROUP BY ru1 UserID       ReferralTotalCount ON ru UserID   ReferralTotalCount UserID   I found that SQL Server was choking on the COUNT    column  and was giving me the error No column was specified for column 2   Putting an alias on the COUNT    column fixed the issue     SELECT               FROM         Users ru         LEFT OUTER JOIN                        SELECT ru1 UserID  COUNT    AS -- gt MyCount lt --             FROM Referral r             LEFT OUTER JOIN Users ru1 ON r ReferredUserId   ru1 UserID             GROUP BY ru1 UserID           ReferralTotalCount ON ru UserID   ReferralTotalCount UserID

User · Answer

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

User · Answer

Just add an alias name as follows sum totalitems  as totalitems

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

Examples related to sql

Examples related to sql-server

Examples related to group-by

Examples related to common-table-expression

Examples related to calculated-columns