Combining tables with same columns in SQL Server

Question

I am trying to combine two tables with same columns in SQL Server.

Individually, my queries work fine:

Select 
    _NAME, COUNT(_NAME) as Occurrences
From 
    SP6815_NOK
Where 
    _VALUE = 1
Group By 
    _NAME 
Order By 
    Occurrences desc

and

Select 
    _NAME, COUNT(_NAME) as Occurrences
From 
    SP6814_NOK
Where 
    _VALUE = 1
Group By 
    _NAME
Order By 
    Occurrences desc

Resulting in:

Occurrences for SP6815_NOK

and

Occurrences for SP6814_NOK

I need the query to perform the exact filters as listed in my individual queries but every time I try a 'join' clause or simply listing both tables under the 'from' statement, I get an ambiguous column error.

I think it might be because some of the messages or "_NAMES" are shared by both tables, but I still need it to count up the occurrences and not overwrite them. Could anyone help?

Are you using MySQL or MS SQL Server? (Don't tag products not involved.) — jarlh, Aug 01 '16 at 14:26
I removed the incompatible database tags. Please add back the tag appropriate to the database you are actually using. — Gordon Linoff, Aug 01 '16 at 14:28

score 3 · Answer 1 · answered Aug 01 '16 at 14:32

If you need the result in separated row then use union

Select _NAME,COUNT(_NAME) as Occurrences
From SP6815_NOK 
Where _VALUE = 1
Group By _NAME 
union
Select _NAME,COUNT(_NAME)
From SP6814_NOK
Where _VALUE = 1
Group By _NAME
Order By Occurrences desc

Use union all if you want all the rows (also duplicated) use only union if you want distinct rows

otherwise if yoe want the result on the same row use fully qualified name

Select SP6815_NOK._NAME,COUNT(SP6815_NOK._NAME)   as Occurrences,  SP6814_NOK._NAME,COUNT(SP6814_NOK._NAME) as Occurrences2
From SP6815_NOK 
INNER JOIN SP6814_NOK ON SP6815_NOK._VALUE = SP6814_NOK._VALUE
Where _VALUE = 1
Group By SP6815_NOK._NAME,  SP6814_NOK._NAME
Order By Occurrences desc

Shnugo · Answer 2 · 2016-08-01T15:24:31.670

A simple UNION ALL will not return the rows sorted the way you seem to need it.

Try this:

EDIT: no sub-selects needed

My first attempt was to complicated. I do not know with which version SQL Server allows to use column aliases in ORDER BY. If this simpler version does not work for you and you are using an older version of SQL Server, you might have a look in the edit history and find the previous code...

Select 'SP6815_NOK' AS SourceTable
        ,NAME
        ,COUNT(_NAME) as Occurrences
From SP6815_NOK 
Where _VALUE = 1
Group By _NAME 

UNION ALL

Select 'SP6814_NOK' AS SourceTable
        ,NAME
        ,COUNT(_NAME) as Occurrences
From SP6814_NOK 
Where _VALUE = 1
Group By _NAME 

ORDER BY SourceTable,Occurrences

UPDATE: column-alias in ORDER BY...

It seems, that this was possible already in SQL Server 2005..., but not in WHERE... I've missed that somehow :-)

Combining tables with same columns in SQL Server

2 Answers2

EDIT: no sub-selects needed

UPDATE: column-alias in ORDER BY...