5 Replies - 7906 Views - Last Post: 05 August 2012 - 07:28 PM

#1 notice88  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 89
  • Joined: 21-December 11

Using Group by to single column and return multiple columns

Posted 05 August 2012 - 02:49 PM

SELECT location, Sequence_Number FROM HRIS_Users GROUP BY location




I want to get the equivalent Sequence number for each location returned from this query, but i always got this error

"Column 'HRIS_Users.Sequence_Number' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
Is This A Good Question/Topic? 0
  • +

Replies To: Using Group by to single column and return multiple columns

#2 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 801
  • View blog
  • Posts: 1,700
  • Joined: 30-January 09

Re: Using Group by to single column and return multiple columns

Posted 05 August 2012 - 03:43 PM

Remove the GROUP BY clause from your query, or add Sequence_Number to the GROUP BY clause.
Was This Post Helpful? 1
  • +
  • -

#3 notice88  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 89
  • Joined: 21-December 11

Re: Using Group by to single column and return multiple columns

Posted 05 August 2012 - 05:04 PM

View Poste_i_pi, on 05 August 2012 - 03:43 PM, said:

Remove the GROUP BY clause from your query, or add Sequence_Number to the GROUP BY clause.


I add the sequence number in the group by clause but i doesnt return what i needed.. I need to group the location field. and also I need to get the sequence number..
Was This Post Helpful? 0
  • +
  • -

#4 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 801
  • View blog
  • Posts: 1,700
  • Joined: 30-January 09

Re: Using Group by to single column and return multiple columns

Posted 05 August 2012 - 05:13 PM

Could you give an example of the data in the table HRIS_Users and an example of what data you want outputted. I think you may be misunderstanding what the GROUP BY clause is for.
Was This Post Helpful? 1
  • +
  • -

#5 notice88  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 89
  • Joined: 21-December 11

Re: Using Group by to single column and return multiple columns

Posted 05 August 2012 - 05:56 PM

View Poste_i_pi, on 05 August 2012 - 05:13 PM, said:

Could you give an example of the data in the table HRIS_Users and an example of what data you want outputted. I think you may be misunderstanding what the GROUP BY clause is for.


8021 Abra
5641 Abra
5475 Accounting Office
5472 Accounting Office
5905 Admin
6743 Admin
6866 Admin
9439 Admin
8758 Admin
10322 Admin

SELECT location, Sequence_Number FROM HRIS_Users GROUP BY location

//I want some output something like this. I want to group the location and somehow the query must also give me some sequence number with this location

5641 Abra
5472 Accounting Office
5905 Admin

I realized something thanks.
Was This Post Helpful? 0
  • +
  • -

#6 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 801
  • View blog
  • Posts: 1,700
  • Joined: 30-January 09

Re: Using Group by to single column and return multiple columns

Posted 05 August 2012 - 07:28 PM

Well, it looks like you're wanting the location, and the minimum value of the sequence numbers. You can achieve that like this:
SELECT MIN(Sequence_Number), location FROM HRIS_Users GROUP BY location


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1