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.

I will point out that when you use NUll in a union to create column in the first select, it will default to an integer datatype. That's fine for this but if it was a varchar or date or decimal field you wanted you should use something like CAST(null as varchar(10)) instead. — HLGEM
– HLGEM, Commented Oct 14, 2010 at 21:41
Compare your SQL to the answer that was given when you asked this question and you'll see that the IP should be the first column in both SELECT statements. — Joe Stefanelli
– Joe Stefanelli, Commented Oct 14, 2010 at 21:44

HLGEM · Accepted Answer · 2010-10-14 21:37:39Z

3

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

edited Oct 14, 2010 at 21:37

HLGEM

97k15 gold badges120 silver badges191 bronze badges

answered Oct 14, 2010 at 21:35

John Hartsock

87.1k23 gold badges135 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HLGEM Over a year ago

You had the columns in the wrong order in the second select, so I fixed them.

Andrew · Accepted Answer · 2010-10-14 21:40:59Z

0

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.

answered Oct 14, 2010 at 21:40

Andrew

14.5k4 gold badges46 silver badges65 bronze badges

Comments

AdaTheDev · Accepted Answer · 2010-10-14 21:37:19Z

0

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

answered Oct 14, 2010 at 21:37

AdaTheDev

148k28 gold badges215 silver badges204 bronze badges

Comments

Gabriel McAdams · Accepted Answer · 2010-10-14 21:37:25Z

0

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

answered Oct 14, 2010 at 21:37

Gabriel McAdams

58.5k12 gold badges67 silver badges78 bronze badges

Collectives™ on Stack Overflow

sql nested select invalid column

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related