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

do you still need to know how to had the netBalance ? – MimiEAM Aug 25 '12 at 05:34 — MimiEAM, Aug 25 '12 at 05:34

Tieran · Answer 1 · 2012-08-25T04:35:33.227

41

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
)

edited Aug 25 '12 at 04:35

answered Aug 25 '12 at 04:23

Tieran

976
6
9

tanks! wierd that you get a default col name on agg expressions in many cases..... – tom Apr 21 '23 at 12:21

Vikdor · Accepted Answer · 2012-08-25T04:26:03.017

35

[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

edited Aug 25 '12 at 04:26

answered Aug 25 '12 at 04:14

Vikdor

23,934
10
61
84

1

Nope! this is not working, for the first one, it showed `Msg 8155, Level 16, State 2, Line 1 No column was specified for column 2 of 'd'. Msg 207, Level 16, State 1, Line 4 Invalid column name 'bkdqty'.` for 2nd one it showed `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'. Msg 207, Level 16, State 1, Line 23 Invalid column name 'duration'. Msg 207, Level 16, State 1, Line 4 Invalid column name 'bkdqty'. ` – Razort4x Aug 25 '12 at 04:17
11

The error is about not naming the aggregate columns in the query that defines 'd' in both the places. I tried to correct it, can you verify? – Vikdor Aug 25 '12 at 04:20
4

f*ck yeah man! It worked! thanks a lot! :-) you are a life savior. One thing though it would be `month(clothdeliverydate) 'duration'` for it to work, otherwise it keeps saying can't find column duration. – Razort4x Aug 25 '12 at 04:26
1

I just had to add aliases to all columns on which a function was used. Even on `RTRIM()` (SQL 2008). And the error was gone. – tedi Aug 02 '17 at 11:33
This was it! Thanks! – mattylantz Feb 08 '22 at 20:12

score 21 · Answer 3 · answered Mar 02 '17 at 04:02

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 -->MyCount<--
            FROM Referral r
            LEFT OUTER JOIN Users ru1 ON r.ReferredUserId = ru1.UserID
            GROUP BY ru1.UserID
        ) ReferralTotalCount ON ru.UserID = ReferralTotalCount.UserID

same for me (well with the AVG function) but after realizing what the problem was I accidentally added the `AS AvgValue` to the *table* name instead of the 'column'. So I ended up with `SELECT Something, Something2, AVG(Value) FROM Orders AS AvgValue`. Looks super stupid here, but the way my SQL was formatted it snuck through and didn't look so obvious! — Simon_Weaver, Nov 09 '18 at 03:46

score 8 · Answer 4 · edited Aug 16 '21 at 08:47

8

A single with clause can introduce multiple query names by separating them with a comma but it's mandatory that every column has a name

In this case, the second query has a column without one:

 as (SELECT 
               duration, 
               sum(totalitems) --**HERE IS A MISSING NAME**
 FROM ...

That's all.

edited Aug 16 '21 at 08:47

Salahuddin Ahmed

4,854
4
14
35

answered Aug 15 '21 at 01:21

Roberto Deleón

81
1
1

score 6 · Answer 5 · answered Aug 25 '12 at 04:37

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!

score 5 · Answer 6 · answered Aug 25 '12 at 10:49

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)           
 )

score 4 · Answer 7 · answered Jan 22 '13 at 07:34

4

Just add an alias name as follows
sum(totalitems) as totalitems.

answered Jan 22 '13 at 07:34

sjith

445
5
7

score 1 · Answer 8 · answered Mar 24 '14 at 12:28

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

score -1 · Answer 9 · answered Aug 25 '20 at 06:41

-1

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

answered Aug 25 '20 at 06:41

user14161796

1

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

9 Answers9