Group by Query question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Group by Query question

Author

Topic

djhughes1
Starting Member

4 Posts

Posted - 2009-11-05 : 15:25:39

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

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2009-11-05 : 15:37:35

Hello djhughes

yes all columns you want to have a group by need to an
aggregate function

SELECT column_name, sum(price), count(quantity)
from table
where clause

Group by price
Group by count

order by price asc

does that make sense

MCTS certified

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-11-05 : 15:40:41

That doesn't really make sense. I think for your example you'd just what to ORDER the results since you are not applying an aggregate function. That would sort of "group" them in order in your result set. For example:

SELECT Title, Last_Name
FROM Employee
ORDER BY Title, Last_Name

But, if you wanted to count the number of Employee's by Title, then you'd do something like:

SELECT Title, COUNT(Title) AS TitleCount
FROM Employee
GROUP BY Title

Does that make sense?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-11-05 : 15:41:40

Group by clause has to have every column in your select clause that you're not aggregating (summing,counting,etc.).

SELECT Title ,Last_Name
FROM Employee
GROUP BY Title ,Last_Name
ORDER BY Title

makes sense.

Jim

Everyday I learn something that somebody else already knew

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-11-05 : 15:45:43

quote:
Originally posted by masterdineen

Hello djhughes

yes all columns you want to have a group by need to an
aggregate function

SELECT column_name, sum(price), count(quantity)
from table
where clause

Group by price
Group by count

order by price asc

Isn't that the exact opposite of what needs to be done? You group by the columns that do not have aggregate functions applied to them. No need (depending on the requirement) to group by columns contained within an aggregate function.

djhughes1
Starting Member

4 Posts

Posted - 2009-11-05 : 15:57:45

quote:
Originally posted by jimf

Group by clause has to have every column in your select clause that you're not aggregating (summing,counting,etc.).

SELECT Title ,Last_Name
FROM Employee
GROUP BY Title ,Last_Name
ORDER BY Title

Having all the columns in the group by clause that were in the select clause is what I was not understanding. So there is no way use the GROUP BY clause with a single column if you have multiple columns within your SELECT?

Thanks for all the quick responses by the way. All of them have been helpful.

djhughes1
Starting Member

4 Posts

Posted - 2009-11-05 : 15:59:24

quote:
Originally posted by Lamprey

That doesn't really make sense. I think for your example you'd just what to ORDER the results since you are not applying an aggregate function. That would sort of "group" them in order in your result set. For example:
SELECT Title, Last_Name
FROM Employee
ORDER BY Title, Last_Name
But, if you wanted to count the number of Employee's by Title, then you'd do something like:
SELECT Title, COUNT(Title) AS TitleCount
FROM Employee
GROUP BY Title
Does that make sense?

It does make sense. I had used the ORDER BY before but was still having problems with the GROUP BY. Both your example you listed are good and helped me out.

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2009-11-05 : 16:12:59

sorry i didnt explain myself properly.

Didnt mean to confuse you. sorry.

djhughes1
Starting Member

4 Posts

Posted - 2009-11-05 : 16:19:24

SO if you want to further break down the query after it has been grouped you have to use the HAVING Clause correct? For example, you have the Title, Last_Name, and also a Wage column. I would need to add the wage intot he query

SO it would be something like


SELECT Title ,Last_Name, Wage
FROM Employee
GROUP BY Title ,Last_Name, Wage
HAVING aggregate_function(column_name) operator value

So in this instance as well, if I do not need the aggregate piece, then I would just need to use the column name?

edit - Let me try to rephrase that as that sounds confusing. Can you group data from one column within another. For example, group by wage within the job title on the query?

edit - I think I got it figured out.


SELECT Wage, Last_Name, Title
FROM Employee
GROUP BY Wage, Last_Name, Title
Order by Title

So that will display a query that has the data grouped by Wage and then within that it is ORDERed by the Title.

Does that seem correct?

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2009-11-06 : 03:50:02

you learn something new everyday on this site.

One day i will be excellent on here. lol

MCTS certified

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources