CODEWITHSUNDEEP
joinssmscommon-table-expressionambiguous
Calflamesfann
Calflamesfann

Reputation: 143

How to resolve Ambiguous column name error SQL

I am using the following query and getting the error, "ambiguous column name 'area'. I have done some reading and found that having it in the CTE and not group by/order by is the cause of the error. Not in my case, it seems. The end result of this query should appear something such as what is below. Any ideas as to where I need to add/subtract a mention of area?

To give some context, the reason for some of the elements is to have the result set contain a pre-determined list of sizeclassep even if there is no data. For some areas, there is no data. A population of 650 does not allow for some industries. Additionally, there are 29 columns in xyzfirms201701. Area, sizeclassep, ownership, and naicscode are just some of the fields contained in it.

The data is confidential and so regrettably no data can be offered 

;with sizeclasseptable as 
(
select area,ownership,sizeclassep from (
select '01' as sizeclassep, '50' as ownership, area='000003'
union select '02' as sizeclassep, '50' as ownership, area='000003'
union select '03' as sizeclassep, '50' as ownership, area='000003'
union select '04' as sizeclassep, '50' as ownership, area='000003'
union select '05' as sizeclassep, '50' as ownership, area='000003'
union select '06' as sizeclassep, '50' as ownership, area='000003'
union select '07' as sizeclassep, '50' as ownership, area='000003'
union select '08' as sizeclassep, '50' as ownership, area='000003'
union select '09' as sizeclassep, '50' as ownership, area='000003') t0
cross join ( select distinct area from xyzfirms201701 ) t1
)

SELECT
'000003' AS area,
t2.[SizeClassep],
COUNT(*) AS [Number of Worksites],
SUM(t2.Employment) AS [Employment In Size Class]
 from sizeclasseptable
 inner join xyzfirms201701 t2 
on t2.area=sizeclasseptable.area 
and t2.ownership=sizeclasseptable.ownership
and t2.sizeclassep = sizeclasseptable.sizeclassep
GROUP BY
t2.area, t2.SizeClassep
ORDER BY
t2.area, t2.SizeClassep


area    SizeClassep Number of Worksites Employment In Size Class
000003  01  10866   13138
000003  02  1275    8322
000003  03  831 11192
000003  04  492 14694
000003  05  116 7783
000003  06  61  8876
000003  07  8   2809
000003  08  11  7909
000003  09  3   5322

Upvotes: 0

Views: 2535

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35583

You have [area] in both the union area='000003' and you have it again in the select distinct area from xyzfirms201701. Which one do you actually want to use as "area"? Given that you appear to only want '000003' in the final result then use t0.area (see. second line below).

If you use a cross join in the cte, I presume you want all rows of that returned, so use a left join instead of inner join (? note, I am guessing this)

You are currently grouping by t2.area but don't include in the select clause. Either omit it from the grouping or include in in the select clause. Note because [area] it is part of the join it can only be whatever value you put into the CTE so I suggest you use sizeclasseptable.area 

;with sizeclasseptable as (
    select t0.area,ownership,sizeclassep 
    from (
                  select '01' as sizeclassep, '50' as ownership, area='000003'
        union all select '02' as sizeclassep, '50' as ownership, area='000003'
        union all select '03' as sizeclassep, '50' as ownership, area='000003'
        union all select '04' as sizeclassep, '50' as ownership, area='000003'
        union all select '05' as sizeclassep, '50' as ownership, area='000003'
        union all select '06' as sizeclassep, '50' as ownership, area='000003'
        union all select '07' as sizeclassep, '50' as ownership, area='000003'
        union all select '08' as sizeclassep, '50' as ownership, area='000003'
        union all select '09' as sizeclassep, '50' as ownership, area='000003'
        ) t0
    /* cross join ( select distinct area from xyzfirms201701 ) t1 */
    )

SELECT
    sizeclasseptable.area  AS [area]
  , t2.SizeClassep
  , COUNT(*)               AS [Number of Worksites]
  , SUM(t2.Employment)     AS [Employment In Size Class]
FROM sizeclasseptable
LEFT JOIN xyzfirms201701 t2 ON t2.area = sizeclasseptable.area
    AND t2.ownership = sizeclasseptable.ownership
    AND t2.sizeclassep = sizeclasseptable.sizeclassep
GROUP BY
    sizeclasseptable.area
  , t2.SizeClassep
ORDER BY
    sizeclasseptable.area
  , t2.SizeClassep

edit

An alternative approach:

DECLARE @ownership varchar(20) = '50'
DECLARE @area varchare(20) = '000003'

WITH sizeclasseptable
AS (
    SELECT
        sizeclassep
    FROM (
                  select '01' as sizeclassep
        union all select '02' as sizeclassep
        union all select '03' as sizeclassep
        union all select '04' as sizeclassep
        union all select '05' as sizeclassep
        union all select '06' as sizeclassep
        union all select '07' as sizeclassep
        union all select '08' as sizeclassep
        union all select '09' as sizeclassep
    ) t0
)

SELECT
    t2.area
  , t2.SizeClassep
  , COUNT(*)           AS [Number of Worksites]
  , SUM(t2.Employment) AS [Employment In Size Class]
FROM sizeclasseptable
LEFT JOIN xyzfirms201701 t2 ON t2.area = @area
AND t2.ownership = @ownership
AND t2.sizeclassep = sizeclasseptable.sizeclassep
GROUP BY
    t2.area
  , t2.SizeClassep
ORDER BY
    t2.area
  , t2.SizeClassep

edit 2

Perhaps a method to reduce the number of query iterations would be to expand the grouping to all 3 of the columns used to select that data, and also broadening the way the where clause is defined. There is also a different method for produce the SizeClassep rows by using values

SELECT
    t2.area
  , t2.SizeClassep
  , t2.ownership
  , COUNT(*)           AS [Number of Worksites]
  , SUM(t2.Employment) AS [Employment In Size Class]
FROM (
    SELECT
        sizeclassep
    FROM (
    VALUES ('01'), ('01'), ('03'), ('04'), ('05'), ('06'), ('07'), ('08'), ('09')
    ) t0 (sizeclassep)
) sizeclasseptable
LEFT JOIN xyzfirms201701 t2 ON t2.sizeclassep = sizeclasseptable.sizeclassep
AND t2.area IN ('0003','0004','0005','0006')  /* ALTER THE LIST TO SUIT YOUR NEEDS */
AND t2.ownership IN ('50','60','70')          /* ALTER THE LIST TO SUIT YOUR NEEDS */
AND 
GROUP BY
    t2.area
  , t2.SizeClassep
  , t2.ownership
ORDER BY
    t2.area
  , t2.SizeClassep
  , t2.ownership

Upvotes: 1

Noor
Noor

Reputation: 165

You have same column name in your cte as well as xyzfirms201701. You have to rename it in any one of the table for this query to work. Alternatively, you can just create alias column name as below. However, the cross join does not make sense here when you do not need any columns from that table. It just creates duplicate records.

The following should work. Notice the a1 alias for area column.

;with sizeclasseptable as 
(
select area,ownership,sizeclassep from (
select '01' as sizeclassep, '50' as ownership, area='000003'
union select '02' as sizeclassep, '50' as ownership, area='000003'
union select '03' as sizeclassep, '50' as ownership, area='000003'
union select '04' as sizeclassep, '50' as ownership, area='000003'
union select '05' as sizeclassep, '50' as ownership, area='000003'
union select '06' as sizeclassep, '50' as ownership, area='000003'
union select '07' as sizeclassep, '50' as ownership, area='000003'
union select '08' as sizeclassep, '50' as ownership, area='000003'
union select '09' as sizeclassep, '50' as ownership, area='000003') t0
cross join ( select distinct area a1 from xyzfirms201701 ) t1
)

SELECT
'000003' AS area,
t2.[SizeClassep],
COUNT(*) AS [Number of Worksites],
SUM(t2.Employment) AS [Employment In Size Class]
 from sizeclasseptable
 inner join xyzfirms201701 t2 
on t2.area=sizeclasseptable.area 
and t2.ownership=sizeclasseptable.ownership
and t2.sizeclassep = sizeclasseptable.sizeclassep
GROUP BY
t2.area, t2.SizeClassep
ORDER BY
t2.area, t2.SizeClassep

Upvotes: 1

Related Questions