Click here to Skip to main content
15,868,141 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
How can combine the secon query below into the first one using subquery? Thanks.
SQL
SELECT  SUM(ORDERITEMS.qty*INVENTORY.PRICE) / COUNT(DISTINCT ORDERS.ORDERID) AS 'Orders_Average'
    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'
   AS 'OrderItems_Average'
  FROM  ORDERITEMS LEFT OUTER JOIN
               INVENTORY ON ORDERITEMS.PARTID = INVENTORY.PARTID RIGHT OUTER JOIN
               ORDERS ON ORDERITEMS.ORDERID = ORDERS.ORDERID
Posted
Updated 19-Jan-13 20:33pm
v3
Comments
Tharaka MTR 20-Jan-13 3:01am    
please specify your output. What do you mean by combine?
7prince 20-Jan-13 11:37am    
adding this query: SELECT SUM(ORDERITEMS.qty*INVENTORY.PRICE) / COUNT(DISTINCT ORDERITEMS.ORDERID) AS 'OrderItems_Average'
AS 'OrderItems_Average'
FROM ORDERITEMS LEFT OUTER JOIN
INVENTORY ON ORDERITEMS.PARTID = INVENTORY.PARTID RIGHT OUTER JOIN
ORDERS ON ORDERITEMS.ORDERID = ORDERS.ORDERID


to this one as a subquery

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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900