Select within a Select

[STEVESKI07]

Hey everyone, I'm having a hard time nesting my Select statements. A simplified version of what I'm trying to do is find the average cost a person spends at a store. A person can make one or multiple purchases, so first I'm getting the total amount they spend and grouping that by the person's name. After that I want to take the average of those numbers, but SQL isn't liking it. This is what I have:

Select AVG(TotalPerCustomer) FROM
(Select Name, SUM(TotalCost) as TotalPerCustomer
From CustomerOrders
Group By Name)

I know usually when you nest Select statements you do it in the WHERE or IN clause, but is it possible to do it in the FROM clause? I know I could always create a temp table and Select from there, but it seems like that is the long way of doing it.

Thanks in advance!

 

[InertiaM]

I dont think your query is 'wrong' - I think it just needs some more qualification in the table names.

Try something like :-

Select
  AVG (TotalledFigures.TotalPerCustomer)
FROM
  (
    Select
      Name,
      SUM (TotalCost) as TotalPerCustomer
    From
      CustomerOrders
    Group By
      Name
  ) TotalledFigures

 

[InertiaM]

I know usually when you nest Select statements you do it in the WHERE or IN clause

I always try and avoid writing queries like that. I believe (although someone with more in-depth knowledge may correct me) that it is better to put SELECTs within FROMs, than it is to use IN. As I understand it, doing something like

SELECT a FROM tblB WHERE a IN (SELECT c FROM tblD)

will run the bracketed query for every single record returned by the first query, whereas if you do

SELECT a FROM (SELECT c FROM tblD INNER JOIN tblB on ....)

will run the subquery once (and first), and then select the records from it.

 

[MattP]

Why do a Sub-Select at all?

SELECT [Name], AVG(TotalCost) As AveragePerCustomer
FROM CustomerOrders
GROUP BY [Name]

If you're needing to know total spent and number of transactions they're easy to implement as well.

SELECT [Name],
	SUM(TotalCost) As TotalPerCustomer,
	COUNT(TotalCost) As TransactionsPerCustomer,
	AVG(TotalCost) As AveragePerCustomer
FROM CustomerOrders
GROUP BY [Name]

-----

If you do need to use a Sub-Query in the future InertiaM is correct that you need to alias your query so that you can use use it in your outer query. You'll also need to get a count of the items that you're summing otherwise your AVG result is always going to be SUM/1.

SELECT sub.[Name],
	sub.Total/sub.Transactions As AveragePerCustomer
FROM (
	SELECT [Name], SUM(TotalCost) As Total,
		COUNT(TotalCost) As Transactions
	FROM CustomerOrders
	GROUP BY [Name]
	) sub

 

[STEVESKI07]

Thanks InertiaM and MattP, it worked perfectly. I just needed to provide an alias for the subquery.

I try to avoid using subquery's as much as possible also, but in this case, I do need a subquery because I want the average total that a person spends, not the average cost per item. A person may have more than one record in the CustomerOrders table, so I have to add those up first before I can find the average of what each person spends. If I just do an average on the CustomerOrders table then I get an average price of each item ordered.

Thanks again for the help and the quick response!

 

[cjard]

Thanks InertiaM and MattP, it worked perfectly. I just needed to provide an alias for the subquery.

A very annoying foible of SQLS, I've found. Oracle either says "must name this expression with an alias" or just does the op, depending on the context.. It's tripped me up plenty of times when working with SQLS

I try to avoid using subquery's as much as possible

There's no need to do so, and SQLserver will probably rewrite any subqueries you do put in if it needs to. Queries that cannot be rewritten are subqueries that perform aggregate functions such as grouping

You should try to avoid use of IN for lists of items that are longer than what you would reasonably type by hand, because IN tends not to perform well under some implementations. While InertiaM's advice that the query is rerun for every row might only apply to some RDBMS engines, IN is often slower than using an inner join

I do need a subquery because I want the average total that a person spends, not the average cost per item.

Indeed, though sqlserver may support this:

SELECT AVG(SUM(cost) OVER(PARTITION BY name)) FROM customerOrders

You may have to add a GROUP BY NULL

-

There is no need to include Name in the selection list of the grouping query:

SELECT AVG(spendPerCust) FROM (SELECT SUM(cost) as spendPerCust FROM customerOrders GRUP BY name) tots

It won't hurt performance, jsut leaves less visual clutter