SQL Innerjoin

Question

Hello,

I have an query like this

Select nodeid, sum(In_TotalBytes/1000000000) as Down , sum(Out_TotalBytes/1000000000) as Up from InterfaceTrafficByDays where nodeid in (select nodeid from nodes where Isserver=1) and month(datetime)=month(getdate())-0 and year (datetime)=year(getdate()) or (year(datetime) = year(getdate()) - 0 and month(datetime) = 12 and month(getdate()) = 1) group by nodeid

And for the result i want to inner join to other table to get the details by change the query to

Select nodeid, sum(In_TotalBytes/1000000000) as Down , sum(Out_TotalBytes/1000000000) as Up from InterfaceTrafficByDays where nodeid in (select nodeid from nodes where Isserver=1) and month(datetime)=month(getdate())-0 and year (datetime)=year(getdate()) or (year(datetime) = year(getdate()) - 0 and month(datetime) = 12 and month(getdate()) = 1) group by nodeid A inner join nodes B on A.nodeid=B.nodeid

but i got an error "Msg 156, Level 15, State 1, Line 6Incorrect syntax near the keyword 'inner'."

Anyone know what should i do?

Answer 1

Hi @Handian Sudianto

Try this:

;with cte as
(
Select nodeid
, sum(In_TotalBytes/1000000000) as Down 
, sum(Out_TotalBytes/1000000000) as Up 
from InterfaceTrafficByDays 
where nodeid in (select nodeid from nodes where Isserver=1) 
and month(datetime)=month(getdate())-0 
and year (datetime)=year(getdate()) 
or (year(datetime) = year(getdate()) - 0 
and month(datetime) = 12 
and month(getdate()) = 1) 
group by nodeid 
)
select A.node, A.Down, A.Up, B.*--columns you want 
from cte A inner join nodes B on A.nodeid=B.nodeid

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.