Click here to Skip to main content
11,704,472 members (55,687 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
How can I modify the query below to show "average dollar value for Orders" and "average dollar value for OrderItems" side by side in columns. The query below would combine them. Thank you.
SELECT (COUNT(Orders.orderid) / SUM(Inventory.price)) AS Orders_Average
FROM  ORDERITEMS LEFT OUTER JOIN
               Inventory ON OrderItems.partid = Inventory.partid RIGHT OUTER JOIN
               Orders ON OrderItems.orderid = Orders.orderid
               
WHERE NOT EXISTS 
(SELECT *   
FROM  ORDERITEMS
WHERE OrderItems.orderid <> Orders.orderid
GROUP BY ORDERITEMS.orderid)
Posted 18-Jan-13 10:20am
7prince409
Edited 18-Jan-13 16:34pm
v2
Comments
Tharaka MTR at 18-Jan-13 22:38pm
   
Orders can have multiple order items. you are asking average value for orders, and average value for order items. could you please let us know how is your expecting output look likes
7prince at 18-Jan-13 23:13pm
   
yes that is correct. I am asking average value for orders, and average value for order items.

I would like to get the output in this format:

two columns: "Orders Average" and "OrderItems Average") or in one column with each output in its own row. Thank you.

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi

For average you need to do Sum over count and not count/sum.
  Permalink  

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 740
1 OriginalGriff 495
2 Maciej Los 355
3 Andy Lanng 284
4 CPallini 267
0 OriginalGriff 8,824
1 Sergey Alexandrovich Kryukov 8,247
2 CPallini 5,197
3 Maciej Los 4,726
4 Mika Wendelius 3,606


Advertise | Privacy | Mobile
Web04 | 2.8.150819.1 | Last Updated 20 Jan 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100