Select Headers union Data with Group and Order not working 1

[Qik3Coder]

Hey,

Trying to work through this and am getting hung up on the fact that it says the columns don't exist:

SELECT'ACCT' as Acct, 'PROJECT' as Project, 'TASK' as Task, 'SUBACCT' as SubAcct, 'TOTAL' as Total, 'DESC' as [Desc], 'SCR' as SCR
UNION
SELECTAcct, Project, Task, SubAcct, cast(Sum(Total) as varchar(13)) as Total, [Desc], SCR 
FROM #Projects
GROUP BY Acct, Project, Task, SubAcct, [Desc], SCR
HAVING Sum(Total) <> 0
ORDER BY CASE WHEN Acct <> 'ACCT' THEN Acct ELSE '' END , SCR, [Desc]

The error that I get is:
Msg 207, Level 16, State 1, Line 8
Invalid column name 'Acct'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'Acct'.
Msg 104, Level 16, State 1, Line 8
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.


Any help with this is greatly appreciated.

David

-Sometimes the answer to your question is the hack that works

 

[PWise]

try

select * from (
SELECT 'ACCT' as Acct, 'PROJECT' as Project, 'TASK' as Task, 'SUBACCT' as SubAcct, 'TOTAL' as Total, 'DESC' as [Desc], 'SCR' as SCR
UNION
SELECT Acct, Project, Task, SubAcct, cast(Sum(Total) as varchar(13)) as Total, [Desc], SCR 
FROM #Projects
GROUP BY Acct, Project, Task, SubAcct, [Desc], SCR
HAVING Sum(Total) <> 0)dt
ORDER BY CASE WHEN Acct <> 'ACCT' THEN Acct ELSE '' END , SCR, [Desc]

 

[Qik3Coder]

I thank you for your knowledge kind sir.

I was able to scratch it, and just write out the headers into an additional datarow in code.

Thanks,

-Sometimes the answer to your question is the hack that works