Solved

sql group by problems

Posted on 2011-09-29
8
179 Views
Last Modified: 2012-05-12
having group by problems

my code:
select ParkID, FirstName + ' ' + LastName as truename
 from tbl_AdminUsers
order by LastName
group by truename;

my error:
Incorrect syntax near the keyword 'group'.

HELP please
0
Comment
Question by:HawaiiDragon
  • 6
  • 2
8 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 36816810
group by FirstName + ' ' + LastName
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 36816816
also, ORDER BY goes after GROUP BY
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 36816821
-- both changes:

select ParkID, FirstName + ' ' + LastName as truename
 from tbl_AdminUsers
group by  FirstName + ' ' + LastName
order by LastName
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 36816833
but what is the purpose of the group by in this instance?  is it just to provide distinct records?  if so, you will either need to group by both columns, or just use distinct:

select distinct ParkID, FirstName + ' ' + LastName as truename
 from tbl_AdminUsers
-- group by  FirstName + ' ' + LastName
order by LastName

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:HawaiiDragon
ID: 36816862
select ParkID, FirstName + ' ' + LastName as truename
 from tbl_AdminUsers
group by  FirstName + ' ' + LastName
order by LastName

Column 'tbl_AdminUsers.ParkID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
0
 
LVL 33

Accepted Solution

by:
knightEknight earned 500 total points
ID: 36816871
yes, you will either need to group by ParkID also, or use DISTINCT.

Also, you will need to ORDER BY one of the selected columns.  You can use the alias in ORDER BY, but not in GROUP BY, so:

select ParkID, FirstName + ' ' + LastName as truename
 from tbl_AdminUsers
group by ParkID, FirstName + ' ' + LastName
order by truename
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 36816879
again, I don't understand why you would need group by here at all, other than to get distinct records.  What do you need this query to show?
0
 

Author Closing Comment

by:HawaiiDragon
ID: 36816897
select ParkID, LastName + ', ' + FirstName as truename
 from tbl_AdminUsers where active = 'Yes'
group by ParkID, LastName + ', ' + FirstName
order by truename

Thank you very much!!!!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now