MSSQL CONCAT query throws Error

Question

I'm tried to execute the below query in MSSQL 2008 R2,but its throwing error. THE QUERY IS:

    SELECT (n_artifactType+(' '+ n_actionPerformed)) AS actionperformed, 
    COUNT(n_actionPerformed) total FROM notifications WHERE   n_project='JupiterQA'
    GROUP BY actionperformed order by n_actionPerformed;

ERROR IS:

Msg 207, Level 16, State 1, Line 1
Invalid column name 'actionperformed'.

Using 'actionperformed' as alias name even though its throwing error. How can I execute above query without error.

please include your table column names – Arunprasanth K V Jan 30 '15 at 06:00 — Arunprasanth K V, Jan 30 '15 at 06:00

Chris du Preez · Accepted Answer · 2015-01-30T06:13:08.267

2

By the looks of it actionperformed is an alias name and not an actual table field (or expression of), which is required by a group by clause.

Suggest group by (n_artifactType+(' '+ n_actionPerformed)) instead.

Interestingly order by would however be happy with an alias.

edited Jan 30 '15 at 06:13

answered Jan 30 '15 at 06:03

Chris du Preez

539
3
8

I'm using DATE(n_received) in MYSQL and I need equivalent in MSSQL. – user3114967 Jan 30 '15 at 06:13
Sorry not sure i am following – Chris du Preez Jan 30 '15 at 06:17
In MSSQL you could use convert, like this `select CONVERT(varchar, GETDATE(),111);` to convert a `DateTime` to a formatted date. – Chris du Preez Jan 30 '15 at 06:23

score 0 · Answer 2 · edited Jan 30 '15 at 06:13

0

The error is in group by section of the query, group by will not work with alias change it to actual expression i.e.(n_artifactType+(' '+ n_actionPerformed))

edited Jan 30 '15 at 06:13

Arunprasanth K V

20,733
8
41
71

answered Jan 30 '15 at 06:10

Rao Y

99
5

MSSQL CONCAT query throws Error

2 Answers2