group by result of subquery

I am trying to use the results of a subquery in my Group By statement, and have found that this is not allowed. I think it may be possible to do this with a CTE, but I'm not sure exactly. I am creating this SQL code for a SQL Server 2005. I am obviously no expert. Here is the SQL statement I wrote...

select
  sum(paxon), 
  (select id from runs as RS where RS.code = RL.runsegment) as port_id,
  date
from
  runlogs as RL
where 
  date BETWEEN '07/09/2012' and '07/16/2012'     
  and account in ('311,312,313')
  and runsegment in (select code from runsegments where org_id = 13)
group by
  date,
  port_id

When I try to run this I am told that port_id is an invalid column name. I am pretty sure that this is possible to do by creating a temporary table or CTE or possibly using a JOIN, but I am not sure how to do so. Any help would be greatly appreciated.

Answers


How about something like this? Unless I am missing something, I don't see a need for a subquery.

SELECT SUM(rl.paxon)
    ,r.id AS port_id
    ,rl.[date]
FROM runlogs rl
    INNER JOIN runs r
        ON r.code = rl.runsegment
    INNER JOIN runsegments rs
        ON rs.code = rl.runsegment
WHERE rl.[date] BETWEEN '20120709' AND '20120716'  
    AND rl.account in ('311','312','313')
    AND rs.org_id = 13
GROUP BY rl.[date], r.id

I'm not sure what account in ('311,312,313') is attempting to do. In my answer, I assumed that account was either nvarchar or varchar and that each of the three items are actually different account values. If account is an int, then the line should be: AND rl.account in (311,312,313).

I also changed your dates to use the preferred unseparated numeric strings.


One option is the below

select sum(sum_paxon), date, port_id from
(
select paxon,

(select id from runs as RS where RS.code = RL.runsegment) as port_id,

date from runlogs as RL

where

date BETWEEN '07/09/2012' and '07/16/2012'

and account in ('311,312,313')

and runsegment in (select code from runsegments where org_id = 13)
 ) Z
group by date, port_id

Need Your Help

Need to Replace String in onclick attribute with dynamic variable

javascript regex replace

What I'm trying to do is replace some a bit of "placeholder" text which is inside a URL string , with another string that is stored inside a javascript variable. This variable is dynamically genera...

Why it echo the whole form again?

javascript php html ajax forms

My problem is when the data is submitted and saved using ajax it gives the response text and after that it echo the whole form again.