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'.
This question is related to
sql
sql-server
group-by
common-table-expression
calculated-columns
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
)
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
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)
)
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
Just add an alias name as follows
sum(totalitems) as totalitems.
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
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!
Source: Stackoverflow.com