How to group by multiple columns using alias in SQL Server?

Question

Grabbed a query from some old code and I'm trying to make it work. Every time I submit the query I get the error:

Error code 207, SQL state S0001: Invalid column name 'Grade'

Code:

SELECT 
    COUNT(*) AS PieceCount
    ,shifttimes.shiftid AS ShiftId
    ,specienames.NameText AS Specie
    ,gradenames.NameText AS Grade
    ,DryerNum
    ,CreatedLocal
FROM 
    sheets, shifttimes, Specienames, GradeNames
WHERE 
    sheets.ShiftIndex = shifttimes.ShiftIndex
    AND sheets.SpecieNameIndex = specienames.NameIndex
    AND sheets.gradenameindex = gradenames.NameIndex
    AND CreatedLocal >= '2015-04-01'
    AND CreatedLocal < '2015-06-01'
GROUP BY 
    ShiftId, Grade, DryerNum, Specie

There's nothing whatsoever to do with Java here. Tags edited. — John Bollinger
– John Bollinger, Commented Sep 29, 2015 at 18:48
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (more than 20 years ago) and its use is discouraged — marc_s
– marc_s, Commented Sep 29, 2015 at 18:57

Juan Carlos Oropeza · Accepted Answer · 2015-09-29 18:54:51Z

5

For the error i think you should change

GROUP BY ShiftId
    ,Grade
    ,DryerNum
    ,Specie

TO

GROUP BY shifttimes.shiftid
    ,gradenames.NameText
    ,DryerNum
    ,specienames.NameText

NOTE:

In your select you have ,CreatedLocal but not in your group by, you have to remove it from the select or include on the group by

SUGGESTION:

Try to use Alias and Implicit joins (having two tables in the from clause) is a deprecated syntax, and it's recommended to switch to the modern, explicit, syntax:

SELECT COUNT(*) AS PieceCount
    ,ST.shiftid AS ShiftId
    ,SN.NameText AS Specie
    ,GN.NameText AS Grade
    ,DryerNum    
FROM 
   sheets S
inner join shifttimes ST
   ON S.ShiftIndex = ST.ShiftIndex
inner join Specienames SN
   ON S.SpecieNameIndex = SN.NameIndex
inner join GradeNames GN
   ON S.gradenameindex = GN.NameIndex
WHERE 
    CreatedLocal >= '2015-04-01'
AND CreatedLocal < '2015-06-01'
GROUP BY 
     ST.shiftid
    ,GN.NameText
    ,DryerNum
    ,SN.NameText

edited Sep 29, 2015 at 18:54

answered Sep 29, 2015 at 18:48

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Bollinger Over a year ago

Yes. The GROUP BY clause cannot use column aliases defined in the SELECT list. From a logic / process flow perspective, grouping happens before selection. Also yes, the select list of an aggregate query must list only grouping columns and / or functions of the groups (though some DBs permit other columns as an extension).

Collectives™ on Stack Overflow

How to group by multiple columns using alias in SQL Server?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related