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
– Arunprasanth K V

2015-01-30 06:00:50 +00:00
Commented Jan 30, 2015 at 6:00 — Arunprasanth K V
– Arunprasanth K V, Commented Jan 30, 2015 at 6:00

Chris du Preez · Accepted Answer · 2015-01-30 06:13:08Z

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, 2015 at 6:13

answered Jan 30, 2015 at 6:03

Chris du Preez

5543 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3114967 Over a year ago

I'm using DATE(n_received) in MYSQL and I need equivalent in MSSQL.

Chris du Preez Over a year ago

Sorry not sure i am following

Chris du Preez Over a year ago

In MSSQL you could use convert, like this select CONVERT(varchar, GETDATE(),111); to convert a DateTime to a formatted date.

Arunprasanth K V · Accepted Answer · 2015-01-30 06:13:41Z

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, 2015 at 6:13

Arunprasanth K V

22.2k8 gold badges51 silver badges80 bronze badges

answered Jan 30, 2015 at 6:10

Rao Y

995 bronze badges

Collectives™ on Stack Overflow

MSSQL CONCAT query throws Error

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related