x
login about faq Site discussion (meta-askssc)

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 '10 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 '10 at 12:16 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.2k 56 63 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



Facebook logo Follow Ask SSC on Facebook
Find Ask SSC on Google+
linkedin logo Find us on LinkedIn

Topics:

x321
x45
x3
x1

asked: Jul 11 '10 at 11:46 AM

Seen: 1348 times

Last Updated: Jul 11 '10 at 12:17 PM

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.
Ask SQL Server Central is a community service provided by Red Gate.