CODEWITHSUNDEEP
sqlsql-servergroup-by
Madison Boman
Madison Boman

Reputation: 11

GROUP BY gives "Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"

So what I am trying to do is set Total_Credits as '12' for Certification, '30' for masters and '120 for bachelors. The Degree_Specialty is what I'm basing my code off of right below it. Im using Microsoft SQL Server Management Studio. This data Im exporting to be an XLS file, however this one minor bug I can get.

Column 'dls.dbo.Acad_Plan_Term.Acad_Plan_Type_Code' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I cant seem to figure out what I'm doing wrong. Its when I change the values'CERT', 'BZ', 'MZ' to '30' '120' and '12' that I start getting these errors.

select 
 z.Degree_Title            
,z.Degree_Specialty
,z.Total_Credits

from
(select
     rtrim(ap.Acad_Plan_Code) as Degree_Title
    ,case when rtrim(ap.acad_plan_type_code) ='MST' and rtrim(ap.Degree_Code) IN ('EMBA','MA','MAA','MBA','MED','MS','MSEE') then ap.Degree_Code
          when rtrim(ap.acad_plan_type_code) ='MST' and rtrim(ap.Degree_Code) NOT IN ('EMBA','MA','MAA','MBA','MED','MS','MSEE') then 'MZ' 
          when rtrim(ap.Acad_Plan_Type_Code) in ('MAJ','BAS','EXT') and RTRIM(ap.Degree_Code) IN ('BA','BAAS','BAS','BBA','BHSA','BS') then ap.Degree_Code
          when rtrim(ap.Acad_Plan_Type_Code) in ('MAJ','BAS','EXT') and RTRIM(ap.Degree_Code) NOT IN ('BA','BAAS','BAS','BBA','BHSA','BS') then 'BZ'
          when Acad_Plan_Type_Code = 'CER' then 'CERT'
     end as Degree_Specialty


    ,case when rtrim(ap.acad_plan_type_code) ='MST' and rtrim(ap.Degree_Code) IN ('EMBA','MA','MAA','MBA','MED','MS','MSEE') then '30'
          when rtrim(ap.acad_plan_type_code) ='MST' and rtrim(ap.Degree_Code) NOT IN ('EMBA','MA','MAA','MBA','MED','MS','MSEE') then '30'
          when rtrim(ap.Acad_Plan_Type_Code) in ('MAJ','BAS','EXT') and RTRIM(ap.Degree_Code) IN ('BA','BAAS','BAS','BBA','BHSA','BS') then '120'
          when rtrim(ap.Acad_Plan_Type_Code) in ('MAJ','BAS','EXT') and RTRIM(ap.Degree_Code) NOT IN ('BA','BAAS','BAS','BBA','BHSA','BS') then '120'
          when Acad_Plan_Type_Code = 'CER' then '12'
     end as Total_Credits
...)z

Upvotes: 0

Views: 1514

Answers (1)

Jacobm001
Jacobm001

Reputation: 4539

The issue is pretty much exactly as the error suggests. If you have a field in the select clause and you want to aggregate a field(s), then all fields which are not in the aggregate must be in the group by clause.

So, for example a valid grouping would be:

select
    departmant
    , sales_person
    , sum(sale_value)
from
   the_table
group by
    department
    , sales_person

You cannot leave sales_person out of the group by clause. If you don't want to group by it, you must leave it out of the select statement.

You haven't specified what database you're using, so it's difficult to give alternatives. your database (oracle, MSSQL, and PostgreSQL) may support windowing.

Upvotes: 2

Related Questions