SQL Group By & Order By

Question

Hi

In my SQL Stored Procedure, i have a column named 'seq' to force the correct order sequence for the return list. Within the data are 7 categories of data and for each category, the sequence is 1 to nth.

I have a Where clause to show only active rows and also to capture the incoming parameter.

If the parameter has a value, filter the data to that value.
If the parameter IS NULL, return all data Group By a column.

SELECT      dbo.TBL_FinishList.prod_number, dbo.TBL_FinishList.alias AS description, 
                dbo.TBL_FinishList.supplier_number, dbo.tbl_FinishGrade.FinishType AS finish_type, dbo.TBL_FinishList.TypeID
    FROM        dbo.TBL_FinishList INNER JOIN
                dbo.tbl_FinishGrade ON dbo.TBL_FinishList.gradeID = dbo.tbl_FinishGrade.ID INNER JOIN
                dbo.tbl_FinishType ON dbo.TBL_FinishList.TypeID = dbo.tbl_FinishType.id LEFT OUTER JOIN
                dbo.tbl_FinishCategory ON dbo.TBL_FinishList.categoryID = dbo.tbl_FinishCategory.ID
    WHERE       (dbo.TBL_FinishList.TypeID <> 0 AND TypeID = @TypeID OR @TypeID IS NULL AND (dbo.TBL_FinishList.active = '1'))
    GROUP BY    dbo.TBL_FinishList.TypeID
    ORDER BY    dbo.TBL_FinishList.seq

This doesnt work when adding Group By and i get a naggregate error so what am i doing wrong? Id like the data list (when all is returned) to be grouped by the dbo.tbl_FinishType column so that when i see the data, all FinishTypes are together and in sequence..

How could i change my statement to achieve that?

Thanks

Nacho

Answer 1

Hi @NachitoMax ,

Group by divides the query result into groups of rows, usually for the purpose of performing one or more aggregations on each group.

If you use GROUP BY without aggregation, all fields after GROUP BY must appear in the SELECT list.

You do not use aggregation in your code. At this time, all the fields after the SELECT must appear in the GROUP BY clause. Otherwise it will return the error you got.

I guess what you want is:

SELECT  dbo.TBL_FinishList.prod_number,   
dbo.TBL_FinishList.alias AS description,   
dbo.TBL_FinishList.supplier_number,   
dbo.tbl_FinishGrade.FinishType AS finish_type,   
dbo.TBL_FinishList.TypeID  
FROM  dbo.TBL_FinishList   
INNER JOIN dbo.tbl_FinishGrade   
ON dbo.TBL_FinishList.gradeID = dbo.tbl_FinishGrade.ID   
INNER JOIN dbo.tbl_FinishType   
ON dbo.TBL_FinishList.TypeID = dbo.tbl_FinishType.id   
LEFT OUTER JOIN dbo.tbl_FinishCategory   
ON dbo.TBL_FinishList.categoryID = dbo.tbl_FinishCategory.ID  
WHERE  (dbo.TBL_FinishList.TypeID <> 0 AND TypeID = @TypeID   
OR @TypeID IS NULL   
AND (dbo.TBL_FinishList.active = '1'))  
ORDER BY  dbo.TBL_FinishList.TypeID,dbo.TBL_FinishList.seq  

If this does not solve your problem, please provide a minimal example of the output you want.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

that worked great thanks. Not sure why i didnt think of that.... :)