sql nested select invalid column

Question

Hey all, I've been struggling with a select statement for a bit now and I was hoping to get some help. I currently have a list IPs in a temporary table and I want to calculate how many times this IP acts as a server and how many times it acts as a client. No matter how I manipluate this select statement I always get the same error telling me that ServerCount is an invalid column, no matter what I replace the nulls with. Here is the select statement:

select IPS, sum (ClientCount) as ClientCount, sum(ServerCount) as ServerCount
from (
       select IP as IPS, Count(*) as ClientCount, null
       from table1 join temp_table 
       on table1.client_ip = temp_table.IP
       group by IP
       union all
       select null,IP as IPS, Count(*) as ServerCount
       from table1 join temp_table 
       on table.server_ip = temp_table.IP
       group by IP
       )t
group by IPS, ClientCount, ServerCount

the first half and the second half work independently without the union. Any ideas as to what is causing this error? Also if I use Nulls I get a second error too. Here is the complete error with nulls in place:

Msg 8155, Level 16, State 2, Line 1 No column was specified for column 3 of 't'. Msg 207, Level 16, State 1, Line 13 Invalid column name 'ServerCount'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'ServerCount'.

Thanks.

Answer 1

You need to define servercount in the first query of the union. Aslo null probably doesnt make sense....I would use zero instead.

select IPS, sum (ClientCount) as ClientCount, sum(ServerCount) as ServerCount
from (
       select IP as IPS, Count(*) as ClientCount, 0 as serverCount
       from table1 join temp_table 
       on table1.client_ip = temp_table.IP
       group by IP
       union all
       select IP as IPS,0 as ClientCount, Count(*) as ServerCount
       from table1 join temp_table 
       on table.server_ip = temp_table.IP
       group by IP
       )t
group by IPS, ClientCount, ServerCount

Answer 2

The first SELECT in the subquery needs to define all the column names so you need to change:

select IP as IPS, Count(*) as ClientCount, null

to

select IP as IPS, Count(*) as ClientCount, null AS ServerCount

Also, I'd change the 2nd SELECT in the subquery to give the columns in the same order:

select IP as IPS, null AS ClientCount, Count(*) as ServerCount

Answer 3

Your query is invalid:

select
    IPS,
    sum(ClientCount) as ClientCount,
    sum(ServerCount) as ServerCount
from (
    select
        IP as IPS,
        Count(*) as ClientCount,
        null as ServerCount
    from table1 join temp_table 
    on table1.client_ip = temp_table.IP
    group by IP

    union all

    select
        null,
        IP as IPS,
        Count(*)
    from table1 join temp_table 
    on table.server_ip = temp_table.IP
    group by IP
)t
group by IPS, ClientCount, ServerCount

When you have a union, they get the column alias from the first query. Your first query needs to specify the ServerCount column name

Answer 4

When doing a UNION ALL, your columns should be in the same order. The query will ordinarily take the names of the first SELECT statement to be the names of the column.

Also, in the outer query, I'm not sure why you are grouping by the aggregate columns; I don't think that will work. I think what you want is probably better served by something like:

SELECT tt.IP,
       (SELECT COUNT(*) FROM table1 t1 WHERE tt.IP = t1.client_ip) AS ClientCount,
       (SELECT COUNT(*) FROM table1 t2 WHERE tt.IP = t2.server_ip) AS ServerCount
FROM   temp_table tt
ORDER BY tt.IP

Much simpler to look at, at least.