0

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?

0

3 Answers 3

4

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)

3
  • This is a really neat rewrite. My only issue is, can you really call it conditional aggregation if it's just AGG(columnname)? I mean I understand that to a degree there's an implied conditional there when it comes to COUNT specifically: count the row if the column is not null. Still, I've always been under the impression that you had to introduce an expression with an actual condition for the aggregation to qualify as conditional. Regardless, the solution is really nice. This is how I like (re)writing my queries as well.
    – Andriy M
    Jun 4, 2023 at 20:35
  • 1
    Yes I would call COUNT(possiblyNull) conditional aggregation, it's conditionally aggregating. Otherwise what else is it doing? Although TBH I originally wrote this using CASE WHEN then realized I could rewrite it shorter Jun 4, 2023 at 21:06
  • This is wonderful and I follow how this is working! Thank you!
    – hobosapien
    Jun 6, 2023 at 16:29
3

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.

1

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

2
  • Not sure I follow - they should be in the first initial 'SELECT *' statement? I tried that and get an error message saying they are invalid column names.
    – hobosapien
    Jun 3, 2023 at 21:58
  • Yes in the top select. What did you put there? Jun 3, 2023 at 22:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.