Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Question -- Get Dollar value of an order withou usiing AVG()  function

Posted on 2013-01-20
3
Medium Priority
?
311 Views
Last Modified: 2013-01-20
I got the query working fine.   How Can I write  to get 'OrderItems_Average' (which is the second query below)  as subquery instead of UNION ALL?

SELECT  SUM(ORDERITEMS.qty*INVENTORY.PRICE) / COUNT(DISTINCT ORDERS.ORDERID) AS 'Orders_Average'
   -- ,  SUM(ORDERITEMS.qty*INVENTORY.PRICE) / COUNT(ORDERITEMS.orderid)  AS 'OrderItems_Average'
  FROM  ORDERITEMS LEFT OUTER JOIN
               INVENTORY ON ORDERITEMS.PARTID = INVENTORY.PARTID RIGHT OUTER JOIN
               ORDERS ON ORDERITEMS.ORDERID = ORDERS.ORDERID

           UNION ALL    

               SELECT  SUM(ORDERITEMS.qty*INVENTORY.PRICE) / COUNT(DISTINCT ORDERITEMS.ORDERID) AS 'OrderItems_Average'
    --,  SUM(ORDERITEMS.qty*INVENTORY.PRICE) / COUNT(ORDERITEMS.orderid)  AS 'OrderItems_Average'
  FROM  ORDERITEMS LEFT OUTER JOIN
               INVENTORY ON ORDERITEMS.PARTID = INVENTORY.PARTID RIGHT OUTER JOIN
               ORDERS ON ORDERITEMS.ORDERID = ORDERS.ORDERID
0
Comment
Question by:ocdc
[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
3 Comments
 
LVL 15

Expert Comment

by:gplana
ID: 38798708
Sorry but your question doesn't make sense. A subquery is a thing very different than an UNION.

Can you please detail what do you pretend to get and what are the definition of the involved tables?

Thanks.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 38798722
if you want the 2 numbers side by side instead of in 2 rows, and as the 2 queries (FROM part) are identical, you can do like this:

SELECT  SUM(ORDERITEMS.qty*INVENTORY.PRICE) / COUNT(DISTINCT ORDERS.ORDERID) AS 'Orders_Average'
   -- ,  SUM(ORDERITEMS.qty*INVENTORY.PRICE) / COUNT(ORDERITEMS.orderid)  AS 'OrderItems_Average'
 , SUM(ORDERITEMS.qty*INVENTORY.PRICE) / COUNT(DISTINCT ORDERITEMS.ORDERID) AS 'OrderItems_Average'
    --,  SUM(ORDERITEMS.qty*INVENTORY.PRICE) / COUNT(ORDERITEMS.orderid)  AS 'OrderItems_Average'
  FROM  ORDERITEMS LEFT OUTER JOIN
               INVENTORY ON ORDERITEMS.PARTID = INVENTORY.PARTID RIGHT OUTER JOIN
               ORDERS ON ORDERITEMS.ORDERID = ORDERS.ORDERID 

Open in new window

0
 

Author Closing Comment

by:ocdc
ID: 38798747
Great. that was what I needed.  Thank you.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

636 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