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.
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