CODEWITHSUNDEEP
sqlt-sqlsql-server-2005
Paul the Tutor
Paul the Tutor

Reputation: 45

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.

Upvotes: 1

Views: 178

Answers (2)

Kevin Aenmey
Kevin Aenmey

Reputation: 13419

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.

Upvotes: 1

iruvar
iruvar

Reputation: 23364

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

Upvotes: 0

Related Questions