question

datalore avatar image
datalore asked

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.
querysub-queryaliastotal
10 |1200 characters needed characters left characters exceeded

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

1 Answer

· Write an Answer
Matt Whitfield avatar image
Matt Whitfield answered
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",
10 |1200 characters needed characters left characters exceeded

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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