Joining two SELECT queries are not joining into a new table

Question

I have two queries pulling information from the same schema which I am joining together to create a single table. However, I need one of the resulting columns. In the example below I need the second 'owneridname' column to be removed from the results.

Current results:

owneridname	Yes	owneridname	No
Name1	230	Name1	400
Name2	345	Name2	546
Name3	87	Name3	2345

Should be:

owneridname	Yes	No
Name1	230	400
Name2	345	546
Name3	87	2345

SELECT *
FROM 
(
SELECT owneridname, COUNT(owneridname) AS 'Yes' FROM incident
WHERE svb_contactname IS NOT NULL 
        AND sub_caseinitiatedfrom IN ('1', '2', '3', '4', '16', '17')
        AND sub_status_case IN ('1','2','12')
    GROUP BY owneridname
) as FirstSet
INNER JOIN
(
SELECT owneridname, COUNT(owneridname) AS 'No' FROM incident
WHERE svb_contactname IS NULL 
        AND sub_caseinitiatedfrom IN ('1', '2', '3', '4', '16', '17')
        AND sub_status_case IN ('1','2','12')
    GROUP BY owneridname
    ) as SecondSet
on FirstSet.owneridname = SecondSet.owneridname
ORDER BY FirstSet.Yes DESC;

When I insert 'owneridname' into the first SELECT statement I get an error of "Msg 209, Level 16, State 1, Line 1 Ambiguous column name 'owneridname'." Not sure why it think it is an ambiguous column name?

Answer 1

As mentioned by others, you need to use table aliases on the columns if you have multiple with the same name. You should really always add the table aliases anyway.

But your query can be significantly simplified, by using conditional aggregation.

SELECT
  i.owneridname,
  COUNT(i.svb_contactname) AS [Yes],
  COUNT(*) - COUNT(i.svb_contactname) AS [No]
FROM incident i
WHERE i.sub_caseinitiatedfrom IN ('1', '2', '3', '4', '16', '17')
  AND i.sub_status_case IN ('1', '2', '12')
GROUP BY
  i.owneridname
ORDER BY
  [Yes] DESC;

COUNT will count up only non-null values. If you want more complex conditions you can do something like COUNT(CASE WHEN yourConditionHere THEN 1 END)

Answer 2

The problem is here:

SELECT * <-- Problem
FROM 
(
SELECT owneridname, COUNT(owneridname) AS 'Yes' FROM incident
WHERE svb_contactname IS NOT NULL 
        AND sub_caseinitiatedfrom IN ('1', '2', '3', '4', '16', '17')
        AND sub_status_case IN ('1','2','12')
    GROUP BY owneridname
) as FirstSet
INNER JOIN
(
SELECT owneridname, COUNT(owneridname) AS 'No' FROM incident
WHERE svb_contactname IS NULL 
        AND sub_caseinitiatedfrom IN ('1', '2', '3', '4', '16', '17')
        AND sub_status_case IN ('1','2','12')
    GROUP BY owneridname
    ) as SecondSet
on FirstSet.owneridname = SecondSet.owneridname
ORDER BY FirstSet.Yes DESC;

You have the same column name in both derived selects, so if you don't specify which of the derived selects you want to pull the column from, it's ambiguous to the query engine which column it should project for the outer select query.

You need to add the correct column aliases to the outer select list, the same was you grasped adding column aliases to the join condition on FirstSet.owneridname = SecondSet.owneridname.

SELECT 
    FirstSet.owneridname,
    SecondSet.owneridname,
    FirstSet.Yes,
    SecondSet.No
FROM 
(
SELECT owneridname, COUNT(owneridname) AS 'Yes' FROM incident
WHERE svb_contactname IS NOT NULL 
        AND sub_caseinitiatedfrom IN ('1', '2', '3', '4', '16', '17')
        AND sub_status_case IN ('1','2','12')
    GROUP BY owneridname
) as FirstSet
INNER JOIN
(
SELECT owneridname, COUNT(owneridname) AS 'No' FROM incident
WHERE svb_contactname IS NULL 
        AND sub_caseinitiatedfrom IN ('1', '2', '3', '4', '16', '17')
        AND sub_status_case IN ('1','2','12')
    GROUP BY owneridname
    ) as SecondSet
on FirstSet.owneridname = SecondSet.owneridname
ORDER BY FirstSet.Yes DESC;

Since Yes and No are unique column names, aliases are considered optional, but are strongly encouraged.

Answer 3

You need to use the derived table name as part of the field list. Select firstset.owneridname, firstset.yes, secondset.no