Group By query question

Question

question

dan 4 asked Nov 5, '09

Group By query question

New to SQL and trying to figure out grouping. So far I have a few questions. When I look at an example from w3schools I see this.

SELECT column_name, aggregate_function(column_name)                    
FROM table_name                    
WHERE column_name operator value                    
GROUP BY column_name

So by looking at this example, my GROUP BY column name has to match the SELECT column name. Is that always the case? Can I group by another column within the same table?

Do I have to have a aggregate_function such as sum? What if I am just trying to group by a certain column?

For example what I am trying to do is this - Display employee name and group them by title.

SELECT Last_Name                    
FROM Employee                    
GROUP BY Title

When I try this I get this error - Column 'Employee.Last_Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Thanks

sql-server-2008 group-by aggregates

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-11-05T18:05:11Z

TG answered Nov 5, '09

Using GROUP BY clause will result in one row for each column combination in the group by clause.

Every column listed in the GROUP BY clause is available to be listed in the SELECT column list but doesn't need to be included. All other column (not in the GROUP BY) can not appear in the SELECT list unless it is used in an aggregate function (like SUM or MAX).

So if you GROUP BY [title] then you will only get one row per distinct title. You could count(last_name) or max(last_name) but you can't just SELECT last_name.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question