x

How to divide by a total from an alias column or sub query

Current working Query:

use pubs
select SUM(ytd_sales) AS YTD_Sales,
       SUM(ytd_sales) AS "Average Sales",
       titles.type AS "Type", 
       COUNT(*) AS "Books Sold"
  from titles
 GROUP BY type;

Creates 4 columns : YTD_Sales , Average Sales, Type , Books Sold

To calculate the "Average Sales" I need to take the ytd_sales / "ALIAS" Books Sold

Now I know I cannot divide by an Alias ("books sold"). Therefore, I created a sub-query

use pubs
select SUM(ytd_sales) AS YTD_Sales,
       SUM(ytd_sales/(SELECT titles.type, COUNT(*) from titles GROUP BY type)) AS "Average Sales",
       titles.type AS "Type", 
       COUNT(*) AS "Books Sold"
  from titles
 GROUP BY type;

But this gives me an error: "Cannot perform an aggregate function on an expression containing an aggregate or a sub-query."

I know there is an easier way but I am just not seeing it.
more ▼

asked Jul 11, 2010 at 11:46 AM in Default

datalore gravatar image

datalore
3 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

A much, much easier way to achieve this would be to use the AVG aggregate.

So, instead of

SUM(ytd_sales) AS "Average Sales",

You just need

AVG(ytd_sales) AS "Average Sales",
more ▼

answered Jul 11, 2010 at 12:16 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x376
x46
x5
x2

asked: Jul 11, 2010 at 11:46 AM

Seen: 2284 times

Last Updated: Jul 11, 2010 at 12:17 PM