67 079
Assignments Done
99,3%
Successfully Done
In November 2018

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

Question #48651
An SQL query to calculate the average amount of invoices for each customer, sorted in ascending order.
Expert's answer
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
 


Need a fast expert's response?

Submit order

and get a quick answer at the best price

for any assignment or question with DETAILED EXPLANATIONS!

Comments

No comments. Be first!

Leave a comment

Ask Your question

Submit
Privacy policy Terms and Conditions