?
Solved

SQL simple select plus average of other lines

Posted on 2013-05-12
5
Medium Priority
?
390 Views
Last Modified: 2013-05-12
Hey guys,
I'm wondering how I can do something directly in the SQL statement without having to do two SQL statements and then have to loop through in code and match it together.

I want to select a list of all open orders, the name of the customer, the amount on the order - and the difficult part (for me) - the average order amount for that customer for orders that are closed.

So in mock up language it becomes something like:
SELECT [OrderNumber], [Customer], [Amount], (AVG([Amount] where Customer = same as this line and [status] = 'closed') AS "Avg Closed Orders Amount"
FROM tblOrders
WHERE [status] = 'open'

Is this possible to do directly in the SQL statement?
Or do I need to first pull all open orders. Then pull the average for closed orders grouped by customer, and then in programming code combine the two?

I can see that there is one very big difference between the two parts of the query. The first part is list per order number. (No group by or select distinct.) The average is grouped by customer, and if a customer has 5 open orders the same average will show up 5 times. And I think that's a problem, but I'm not sure if there are any ways around it.
0
Comment
Question by:VikingOnline
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 8

Accepted Solution

by:
rpkhare earned 1200 total points
ID: 39159597
Have you tried something like:

SELECT A.[OrderNumber], A.[Customer], A.[Amount], 
(SELECT AVG(Amount) FROM tblOrders B where B.Customer = A.Customer AND status = 'Closed') AS "Avg Closed Orders Amount"
FROM tblOrders A
WHERE [status] = 'open'
GROUP BY A.OrderNumber, A.Customer,A.Amount

Open in new window

0
 

Author Closing Comment

by:VikingOnline
ID: 39159668
Just what I was looking for.

I can't believe how close I got in some of my attempts before giving up by the entire needing to GROUP BY, not thinking that I could just group by all levels of the details.

Thank you for the quick reply.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39160129
that style of correlated subquery (inside the selection clause) will be OK for small selections, but it won't perform very well for bigger selections. You could try this style of approach as an alternative
SELECT
      A.[OrderNumber]
    , A.[Customer]
    , A.[Amount]
    , B.avg_clord_amt AS "Avg Closed Orders Amount"
FROM tblOrders A
LEFT JOIN (
            SELECT [Customer], AVG(Amount) avg_clord_amt
            FROM tblOrders
            WHERE STATUS = 'Closed'
            GROUP BY Customer
           ) AS B ON A.Customer = B.Customer 
WHERE A.[status] = 'open'

Open in new window

0
 

Author Comment

by:VikingOnline
ID: 39160311
So subquery was my initial thought and made sense to me.
I never thought about linking the table to itself.

How big would the data set be before you would see issues with subqueries?
(In this case, there can be a lot of orders - even if I put a date range on it.)

Sorry, I already awarded the points. Not sure if I can get that changed and split it, since it seems to be two good solutions to this.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39160364
both approaches use subqueries :)
select
...
, (select x from y where a.id = y.id) /* correlated, in the selection list */

select
...
from a
left join ( select ...) y  on a.id = y.id
hard to put a number on when the lower approach will outperform the upper, but it most probably will. If you are doing a date range selection think about using the same or similar selection inside the group by too.

and if doing date range selection don't use 'between'
see: "Beware of Between"

regarding points that's up to you, I'm fine as it is, but as a fyi you can ask for questions to be re-opened I believe, or simply ask another question.

The answer provided was fine for the question asked
0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question