CODEWITHSUNDEEP
sqlsql-server
Amr Ibrahim
Amr Ibrahim

Reputation: 177

SQL Group By two columns

just trying to group selected records by two columns 

SELECT FSB_ProcessItems.InvId,
       FSB_ProcessItems.ItemId,
       FSB_Processes.Code,
       Sum(FSB_ProcessItems.Qty),
       FSB_Processes.Date

FROM FSB_ProcessItems 
     INNER JOIN FSB_ProcessStudents ON FSB_ProcessItems.ProStudentId = FSB_ProcessStudents.ProStudentId
     INNER JOIN FSB_Processes ON FSB_Processes.ProcessId =  FSB_ProcessStudents.ProcessId 


GROUP BY FSB_ProcessItems.InvId, FSB_ProcessItems.ItemId

Bu I have the errors:

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

on FSB_Processes.Code and FSB_Processes.Date

Before trying to group the records are selected correctly, so I'm just having a problem with grouping

Searched for solution but didn't have much luck since I don't fully understand the problem

so I'm looking for an explanation and solution.

Upvotes: 0

Views: 92

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You need to decide what you want. As written, you should take the Code and the Date out of the SELECT:

SELECT pi.InvId, pi.ItemId, Sum(pi.Qty)
       -- p.Code, p.Date
FROM FSB_ProcessItems pi INNER JOIN
     FSB_ProcessStudents s
     ON pi.ProStudentId = s.ProStudentId INNER JOIN
     FSB_Processes p
     ON p.ProcessId =  s.ProcessId 
GROUP BY pi.InvId, pi.ItemId;

If you want a separate row for codes and/or dates, then include themin the GROUP BY.

Upvotes: 1

Judith Palacios
Judith Palacios

Reputation: 413

you must put in the group by clause all the selected columns that don´t have an aggregate function

SELECT FSB_ProcessItems.InvId,
   FSB_ProcessItems.ItemId,
   FSB_Processes.Code,
   Sum(FSB_ProcessItems.Qty),
   FSB_Processes.Date

FROM FSB_ProcessItems 
 INNER JOIN FSB_ProcessStudents ON FSB_ProcessItems.ProStudentId =   FSB_ProcessStudents.ProStudentId
 INNER JOIN FSB_Processes ON FSB_Processes.ProcessId =  FSB_ProcessStudents.ProcessId 

GROUP BY FSB_ProcessItems.InvId, FSB_ProcessItems.ItemId, FSB_Processes.Date, FSB_Processes.Code

Upvotes: 1

Related Questions