?
Solved

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

Posted on 2013-01-20
3
Medium Priority
?
308 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

764 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