Problem with grouping - 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

Problem with grouping

Author

Topic

snssewell
Starting Member

14 Posts

Posted - 2009-06-23 : 13:41:49

I am trying to write a SQL statement that sums an average of sales and then groups it by type and displays an average of sales and how many books were sold.
This is what I have so far

SELECT SUM(t.ytd_sales)AS YTD_Sales, t.type, s.qty
	FROM titles t, sales s
	GROUP BY t.type, s.qty

But it should look like this
YTD SALES______Average_____Type___________Books Sold
30,000_________7,000_______Business_______4
24,000_________12,000______Cooking 2
etc

I can not get it to group it right. I am using VisualStudio 08 Pro to do this as a stored procedure. So when I take the GROUP BY s.qty out I get a error that it has to be in the GROUP BY Clause or an aggregate function. I am also not sure how to get the Average. I guess I would have to divide the YTD_Sales by Books sold but not sure how and where to put it. Can someone help please?

~Silke~

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-23 : 13:48:00

Have you tried: AVG()

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-23 : 13:48:58

I meant
AVG(t.ytd_sales)

No, you're never too old to Yak'n'Roll if you're too young to die.

snssewell
Starting Member

14 Posts

Posted - 2009-06-23 : 14:14:44

quote:
Originally posted by webfred

I meant
AVG(t.ytd_sales)

No, you're never too old to Yak'n'Roll if you're too young to die.

Ok this worked so I have this now:

SELECT SUM(t.ytd_sales)AS YTD_Sales,AVG(t.ytd_sales)AS Average_Sales, t.type, s.qty
	FROM titles t, sales s
	WHERE t.title_id = s.title_id
	GROUP BY s.qty, t.type

But I need it grouped by only the type. But when I take the s.qty out I still get that error. Any way around it?
The error is:
Column sales.qty is invalid in the selected list because it is not contained in either a aggregate function or GROUP BY Clause.
When I leave it there it works but it does not group it by Type.

I am thinking I have to use a JOIN but when I put JOIN or INNER JOIN in the FROM clause I get the error Incorrect syntax near WHERE. Any ideas?
Here is how I had it when I got the error:

SELECT SUM(t.ytd_sales)AS YTD_Sales,AVG(t.ytd_sales)AS Average_Sales, t.type, s.qty
	FROM titles t INNER JOIN sales s
	WHERE t.title_id = s.title_id
	GROUP BY s.qty, t.type

~Silke~

eonmantra
Starting Member

11 Posts

Posted - 2009-06-23 : 14:45:55

You are getting that error message because logically what you are doing by using the SUM or AVG functions is collapsing many rows into fewer rows. By not specifying s.qty as an aggregate or in the GROUP BY clause it cannot collapse, and therefore SQL has no way of knowing what to do with it.

My first guess looking at your query is that you probably want to SUM(s.qty) to which will give you the quantity per group.

Your join query is missing the ON statement.

Code without join:


SELECT SUM(t.ytd_sales)AS YTD_Sales,AVG(t.ytd_sales)AS Average_Sales, t.type, SUM(s.qty) AS qty
	FROM titles t, sales s
	WHERE t.title_id = s.title_id
	GROUP BY t.type

Code with join: (note I didn't know the names of your join fields so you will have to edit this slightly)


SELECT SUM(t.ytd_sales)AS YTD_Sales,AVG(t.ytd_sales)AS Average_Sales, t.type, SUM(s.qty) AS qty
	FROM titles t INNER JOIN sales s
          ON t.fieldname = s.fieldname
	WHERE t.title_id = s.title_id
	GROUP BY t.type

snssewell
Starting Member

14 Posts

Posted - 2009-06-23 : 14:59:46

quote:
Originally posted by eonmantra

You are getting that error message because logically what you are doing by using the SUM or AVG functions is collapsing many rows into fewer rows. By not specifying s.qty as an aggregate or in the GROUP BY clause it cannot collapse, and therefore SQL has no way of knowing what to do with it.

My first guess looking at your query is that you probably want to SUM(s.qty) to which will give you the quantity per group.

Your join query is missing the ON statement.

Code without join:

SELECT SUM(t.ytd_sales)AS YTD_Sales,AVG(t.ytd_sales)AS Average_Sales, t.type, SUM(s.qty) AS qty
FROM titles t, sales s
WHERE t.title_id = s.title_id
GROUP BY t.type

Code with join: (note I didn't know the names of your join fields so you will have to edit this slightly)

SELECT SUM(t.ytd_sales)AS YTD_Sales,AVG(t.ytd_sales)AS Average_Sales, t.type, SUM(s.qty) AS qty
FROM titles t INNER JOIN sales s
ON t.fieldname = s.fieldname
WHERE t.title_id = s.title_id
GROUP BY t.type

Thanks .... I realized the ON after posting this but the SUM(s.qty) really helped. So now it groups it like I want. Thanks.

~Silke~

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources