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

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
ocdcAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
gplanaCommented:
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
 
ocdcAuthor Commented:
Great. that was what I needed.  Thank you.
0
All Courses

From novice to tech pro — start learning today.