CODEWITHSUNDEEP
sqlsql-serverunion
Roi811
Roi811

Reputation: 67

select top values from union of two tables

trying to select the top 5 countries out of a union but it doesnt seem to be working. this is my code:

SELECT TOP 5 country_number, country_name 
FROM
(
    SELECT country_number, country_name, COUNT(DISTINCT number)
    FROM
    (
        SELECT tc.country_number, tc.country_name, tdc.number, count(*) as tot_cnt
        FROM tblCountry tc
        INNER JOIN tblDivingClub AS tdc   ON tdc.country = tc.country_number
        INNER JOIN tblWorks_for  AS tw    ON tw.club_number = tdc.number
        INNER JOIN tblDiver      AS td    ON td.diver_number = tw.diver_number
        WHERE tw.end_working_date IS NULL
        GROUP BY tc.country_number, tc.country_name, tdc.number
        HAVING count(*) > 1
    ) as der
    GROUP BY country_number, country_name

    UNION 

    SELECT country_number, country_name, COUNT(DISTINCT number)
    FROM
    (
        SELECT tc.country_number, tc.country_name, tdc.number, count(*) as diveCount
        FROM tblCountry tc
        INNER JOIN tblDivingClub AS tdc   ON tdc.country = tc.country_number
        INNER JOIN tblDiving  AS tdv    ON tdv.diving_club = tdc.number
        WHERE tdv.date_of_diving >= DATEADD(year,-1, GETDATE())
        GROUP BY tc.country_number, tc.country_name, tdc.number
        HAVING count(*) > 6
    ) as der2
    GROUP BY country_number, country_name
)as combinedTable

it wont accept "combinedTable" or any other name / command i place after "as".

Upvotes: 2

Views: 474

Answers (3)

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7171

You may be able to simplify your query, untested:

SELECT TOP 5 country_number, country_name
FROM
(
    SELECT tc.country_number, tc.country_name, tdc.number, count(*) as tot_cnt
    FROM tblCountry tc
    JOIN tblDivingClub AS tdc   ON tdc.country = tc.country_number
    JOIN tblWorks_for  AS tw    ON tw.club_number = tdc.number
    JOIN tblDiver      AS td    ON td.diver_number = tw.diver_number
    WHERE tw.end_working_date IS NULL
       OR tdv.date_of_diving >= DATEADD(year,-1, GETDATE())
    GROUP BY tc.country_number, tc.country_name, tdc.number
    HAVING count(*) > CASE WHEN tw.end_working_date IS NULL THEN 1 ELSE 6 END
) as T

Upvotes: 0

Leandro Gomes
Leandro Gomes

Reputation: 568

You can try specify a column name for 

COUNT(DISTINCT number)

or even removing this column from both selects.

Upvotes: 1

AaronLS
AaronLS

Reputation: 38367

On your first query of the union you need a alias for the aggregate count. All columns must have a name in the first query of a union. Since you are using a function to calculate a value you must use an alias to assign it a name:

SELECT country_number, country_name, COUNT(DISTINCT number) as countNumber

Don't you love that the structure of SQL lends it to suggest the problem with your code is on the complete opposite end of the code file? :)

Upvotes: 1

Related Questions