SQL query - count with subquery

Question

I want to get the count of all the columns that are retrieved in the query.I have used the below code:

select count (*) 
from ( 
   select distinct ID,salary,name,location 
   from test 
) ;

I am getting an error message:

Incorrect error at ; expecting AS,ID or quoted_ID

When I add as below:

select count (*) 
from (
   select distinct ID,salary,name,location 
   from test 
) as count;

The query works now but the column name is not renamed to the alias given. What is the logic behind this?

Some DBMS require derived tables to have an alias, some don't. Which DBMS are you using? — user330315
– user330315, Commented Apr 17, 2017 at 20:57
I am using Microsoft SQL Server 08,My concern is even after giving the alias name the column name is not changed,it is displayed as No column name. — akash
– akash, Commented Apr 17, 2017 at 21:27
You gave the derived table an alias, not the column. If you want to give the column an alias you have to specify it there, count(*) as count from (...) as x — user330315
– user330315, Commented Apr 18, 2017 at 6:05

Dinesh Karn · Accepted Answer · 2017-04-17 20:55:33Z

1

In Sql you have to give alias to subquery. So that's the reason the second query works and first one fails

answered Apr 17, 2017 at 20:55

Dinesh Karn

811 silver badge3 bronze badges

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

1 Comment

akash Over a year ago

The second query works but the column name is not renamed with the alias name given in query,it is displayed as "no column name"

RoMEoMusTDiE · Accepted Answer · 2017-04-17 20:56:05Z

1

i don't know what you are trying to achieve but to correct this would be

select count (*) from (select distinct ID,salary,name,location from test ) as myTAble;

subquery will act as your table to query from therefore it needs a name or alias

answered Apr 17, 2017 at 20:56

RoMEoMusTDiE

4,8341 gold badge19 silver badges26 bronze badges

2 Comments

akash Over a year ago

Alias name should be used just after the column which is supposed to be renamed that is before "from" .Anyhow in this code,the column name is displayed without the alias name

RoMEoMusTDiE Over a year ago

will to put it officially a derived table needs to have a name as far as i know

Haroon Yousuf · Accepted Answer · 2018-05-09 11:52:41Z

1

You are giving alias to the table and not column. The following query would work.

select count (*) As count
    from (
       select distinct ID,salary,name,location 
       from test 
    ) as tbl;

answered May 9, 2018 at 11:52

Haroon Yousuf

5244 silver badges9 bronze badges

Collectives™ on Stack Overflow

SQL query - count with subquery

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related