CODEWITHSUNDEEP
sqlsql-serverview
user3638739
user3638739

Reputation: 40

SQL query to view top customers

I am trying to make a view to see top 10 customers and this is what I have so far, what am I doing wrong?

CREATE VIEW vwTopCustomer
AS 
   SELECT TOP 10 * 
       (CustomerID) AS TotalMoneySpent, 
       SUM(Quantity*Price)
   FROM 
       tblCUSTOMER c
   JOIN 
       tblORDER o ON c.CustomerID = o.CustomerID
   JOIN 
       tblORDER_PRODUCT op ON o.OrderID = op.OrderID
   JOIN 
       tblPRODUCT p ON op.ProductID = p.ProductID
   GROUP BY  
       CustomerID
   ORDER BY 
       TotalMoneySpent DESC
   GO

Upvotes: 0

Views: 1435

Answers (3)

M.Ali
M.Ali

Reputation: 69494

Mind you SQL Server will get TOP 10 rows but may or may not show in the order you expect it to be shown.

This is a limitation with sql server. The returned rows will be "Correct" TOP 10 but not in the order of the intended ORDER BY clause.

You will need to use the ORDER BY clause when selecting from your view. 

CREATE VIEW vwTopCustomer
AS 
   SELECT TOP 10  CustomerID 
             ,SUM(Quantity*Price) AS TotalMoneySpent
   FROM  tblCUSTOMER c
   JOIN  tblORDER o          ON c.CustomerID = o.CustomerID
   JOIN  tblORDER_PRODUCT op ON o.OrderID = op.OrderID
   JOIN  tblPRODUCT p        ON op.ProductID = p.ProductID
   GROUP BY   CustomerID
   ORDER BY  TotalMoneySpent DESC
GO

Select from View

SELECT * FROM vwTopCustomer
ORDER BY TotalMoneySpent DESC

ORDER BY in Views

Order the result set of a query by the specified column list and, optionally, limit the rows returned to a specified range. The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified.

BOL Referrence

Upvotes: 1

Reisclef
Reisclef

Reputation: 2148

Without seeing the data, I can't be sure, but the two things I noticed is that you selected top 10 * then the two fields. Also, the second column is probably what you'll want to order by desc.

CREATE VIEW vwTopCustomer
AS 
   SELECT TOP 10 
       CustomerID , 
       SUM(Quantity*Price) AS TotalMoneySpent
   FROM 
       tblCUSTOMER c
   JOIN 
       tblORDER o ON c.CustomerID = o.CustomerID
   JOIN 
       tblORDER_PRODUCT op ON o.OrderID = op.OrderID
   JOIN 
       tblPRODUCT p ON op.ProductID = p.ProductID
   GROUP BY  
       CustomerID
   ORDER BY 
       TotalMoneySpent DESC
   GO

Upvotes: 1

SoulTrain
SoulTrain

Reputation: 1904

You cant group by a column and then Select * in your query result.

Try this edited version..

CREATE VIEW vwTopCustomer
AS 
   SELECT TOP 10 
       (CustomerID), 
       SUM(Quantity*Price) AS TotalMoneySpent
   FROM 
       tblCUSTOMER c
   JOIN 
       tblORDER o ON c.CustomerID = o.CustomerID
   JOIN 
       tblORDER_PRODUCT op ON o.OrderID = op.OrderID
   JOIN 
       tblPRODUCT p ON op.ProductID = p.ProductID
   GROUP BY  
       CustomerID
   ORDER BY 
       2 DESC
   GO

Upvotes: 1

Related Questions