Solved

sql group by problems

Posted on 2011-09-29
8
228 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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
 

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 our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
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 …
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

688 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