Answer to Question #48651 in Databases | SQL | Oracle | MS Access for user

Question

An SQL query to calculate the average amount of invoices for each customer, sorted in ascending order.

Answer 1

Question 1.

IF OBJECT_ID(N'Invoice', N'U') IS NOT NULL
DROP TABLE [dbo].[Invoice]
GO

CREATE TABLE [dbo].[Invoice](
[Invoice_id] [numeric](18, 0) NOT NULL Primary Key,
[Invoice_date] [date]NULL,
[Invoice_Total_amount] [decimal](18, 2) NULL,
[Customer] [varchar](50) NOT NULL,
)
GO

Insert Into Invoice ([Invoice_id], [Invoice_date], [Invoice_Total_amount],[Customer]) Values
(1, '2014/11/01', 100, 'John Smith'),
(2, '2014/11/01', 100, 'John Smith'),
(3, '2014/11/01', 100, 'John Smith'),
(4, '2014/11/01', 100, 'John Connors'),
(5, '2014/11/01', 100, 'John Connors'),
(6, '2014/11/02', 100, 'John Smith'),
(7, '2014/11/02', 100, 'John Smith'),
(8, '2014/11/02', 100, 'John Smith'),
(9, '2014/11/02', 100, 'John Smith'),
(10, '2014/11/02', 100, 'John Smith'),
(11, '2014/11/02', 100, 'John Connors'),
(12, '2014/11/02', 100, 'John Connors'),
(13, '2014/11/02', 100, 'John Connors')
Go

Select
Customer
,Avg(Cast(InvoiceCount As Float)) InvAvg
From
(
Select
Customer
,Invoice_date
,Count([Invoice_id]) InvoiceCount
From
Invoice
Group by
Customer
,Invoice_date
) As InvCount
Group by
Customer
Order by
InvAvg
Go