Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (2)

Resolved Aggregate Functions or the GROUP BY clause :

  1. How-to
0
Votes
Undo
  1. mahmudattar salah
    mahmudattar salah
  2. PowerBuilder
  3. Monday, 22 April 2024 13:25 PM UTC

 

I'm using SQL SERVER 2014

I nead Ignore this error . GROUP BY with Aggregate Functions SLQSTAT = 42000

My problem is while executing this query

select empId, empName, empstate, sum(empsalary)
from emp
group by
empId, empName ;

Result :
COLUMN empId . is invalid in the select list because it is not contained in either an Aggregate Functions or the GROUP BY clause .

  1. ASE
  2. SQL Server
Copy CLIB windows to new powerbuilder instal
How to handle nested SetRedraw() pairs, a modest p...
Responses (2)
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Monday, 22 April 2024 15:27 PM UTC
  2. PowerBuilder
  3. # 1

Hi;

  This is an SS issue in your DML statement being parsed by SS. You'll need to resolve this error at the SQL Server level. 

Regards ... Chris 

Comment
There are no comments made yet.
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Monday, 22 April 2024 15:19 PM UTC
  2. PowerBuilder
  3. # 2

I suspect the error message actually lists "empstate" as the offending column when you transcribed the message text.

This message/error is produced by the SQL Server database, not PowerBuilder. You can use an aggregate function such as SUM(xxx) with additional columns in the SELECT statement only when all of the other columns are listed in the GROUP BY clause. In your case, adding empstate to the GROUP BY clause will resolve the error.

Best regards, John

Comment
mahmudattar salah
  1. mahmudattar salah
  2. Tuesday, 23 April 2024 11:38 AM UTC
Thanks -john-fauss

this an simple example in my select . I Nead To ignore adding [empstate] to the GROUP BY

I have thousands data window nead update groups

how can do this ?
  1. Helpful
John Fauss
  1. John Fauss
  2. Tuesday, 23 April 2024 13:30 PM UTC
You cannot ignore this error condition.

When you modify the SQL to add the empstate column to the SELECT clause, you have two possible options,

1. Use an aggregate function with the empstate column, for example MIN(empstate) or MAX(empstate), if appropriate. Including empstate in an aggregate function negates the need to include empstate in the GROUP BY clause. Or...

2. Include empstate in the GROUP BY clause.

This is how SQL (not just SQL Server, but SQL in general) works. This is a SQL syntax rule...you cannot ignore it.
  1. Helpful 1
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.