Solved

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

Posted on 2013-01-20
3
301 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
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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
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 …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

863 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now