how to get average number of Items per invoice...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

how to get average number of Items per invoice...

Author

Topic

gvmk27
Starting Member

44 Posts

Posted - 2011-12-16 : 10:50:44

Hi

I need a SQL to fetch Average number of lines per invoice for current year.

I have Invoice table and Invoice Items table.

Invoice table has OrderNumber, OrderDate and Items table has ItemNumber,OrderNumber...

select COUNT(ItemNumber) as ItemCount from OrderItem where OrderNumber IN (select OrderNumber from OrderSummary where CreateDate between '01/01/2011' and '12/31/2011'
and GPTransfer = 1) group by OrderNumber order by ItemCount

but how to get the average number of items per invoice as some invoice can have 2 items, some can have 100 items..

Thanks for your help.
MOhan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-16 : 10:55:36

[code]
SELECT AVG(ItemCnt*1.0) AS AvgItemsPerInvoice
FROM
(
SELECT i.OrderNumber,COUNT(it.ItemNumber) AS ItemCnt
FROM Invoice i
INNER JOIN InvoiceItems it
ON it.OrderNumber = i.OrderNumber
GROUP BY i.OrderNumber
)t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-16 : 10:57:03

SELECT AVG(COUNT_OrderNumber*1.00) AS AVG_COUNT_OrderNumber
(SELECT Order_Number, COUNT(*) AS COUNT_OrderNumber
FROM Invoice in INNER JOIN Items it
ON in.OrderNumber = it.OrderNumber
GROUP BY in.OrderNumber
) AS XXX

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

gvmk27
Starting Member

44 Posts

Posted - 2011-12-16 : 11:28:00

Thank you guys...

Regards
MOhan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-16 : 13:12:18

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-16 : 14:36:01

Please make sure you label ALL your columns

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources