CODEWITHSUNDEEP
sqlsql-server
Vincent
Vincent

Reputation: 155

Retrieving records from other table column

I created a view where there are multiple column. I need to retrieve some data from an external table. enter image description here enter image description here

cte4 as (
SELECT *,
case 
    when roadName=roadNameUserData then sec1
else 'x' 
end as roadCrossSection1

FROM dbo.roadSectionAndPavementUserData,cte3
    )

--"roadName" is from cte computed column view
--"roadNameUserData" is from other table("dbo.roadSectionAndPavementUserData") where records are to be extracted
--sec1 is the column in table "dbo.roadSectionAndPavementUserData" where the results coming from 



-----final view
SELECT roadName,
sum(roadLength) as sumRoadLength,roadCrossSection1


FROM cte4 
group by roadName
order by roadName

When i execute the query, it came with the following error

"Column 'cte4.roadCrossSection1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

enter image description here

This is the result after adding additional groupby statement. The correct records are the lesser values of sumRoadLength.

Upvotes: 0

Views: 87

Answers (3)

Nguyễn Văn Phong
Nguyễn Văn Phong

Reputation: 14228

You need to group by roadCrossSection1 as well

SELECT roadName, roadCrossSection1,
               sum(roadLength) as sumRoadLength
FROM cte4 
group by roadName, roadCrossSection1
order by roadName, roadCrossSection1

Updated

SELECT roadName, sum(roadLength) as sumRoadLength
FROM cte4 
group by roadName 
order by roadName

Upvotes: 1

forpas
forpas

Reputation: 164194

From your comment:

I observed that the correct result should be the lesser values from roadName records and those values that are not nulls

I think that you need conditional aggregation:

SELECT roadName,
  sum(case when roadCrossSection1 is not null then roadLength end) as sumRoadLength,
  max(roadCrossSection1) roadCrossSection1
FROM cte4 
group by roadName
order by roadName

Upvotes: 1

Appy
Appy

Reputation: 93

Error says if you want roadCrossSection1 in select clause either you have to apply any aggregate function e.g MIN, Max, Sum, Avg on roadCrossSection1 or you have to put it in group by clause because roadCrossSection1 might have multiple values for same road name but at same time you are evaluating only one value for roadLength by calling aggregate function on roadLength

Upvotes: 0

Related Questions