CODEWITHSUNDEEP
sqlsql-server
Grootpoot
Grootpoot

Reputation: 53

Group and Ordering in SQL

I need to make a query in SQL that can display the top selling book. for this I need to do a inner join and I get a problem after I did the inner join. Here is my code:

CREATE VIEW mostpopularbookssold AS 
SELECT     Count(orders.book_id) AS numberofbookssold , 
           Top(1) books.[Name] 
FROM       orders 
INNER JOIN books 
ON         books.id=orders.book_id 
GROUP BY   books.[Name] 
select * 
FROM   mostpopularbookssold

Upvotes: 1

Views: 77

Answers (1)

Christos
Christos

Reputation: 53958

The TOP(1) should be placed at the start of your query, after SELECT. Furthermore you have to make an ordering by the books sold:

CREATE VIEW MostPopularBooksSold
AS
SELECT TOP(1)
    COUNT(Orders.Book_ID) AS NumberOfBooksSold
    , Books.[Name] 
FROM [the database name].[schema name].[Orders] AS Orders
INNER JOIN [the database name].[schema name].[Books] AS Books
    ON Books.ID = Orders.Book_ID
GROUP BY Books.[Name]
ORDER BY COUNT(Orders.Book_ID) DESC

You should replace the database name with the name of the database, in which you have created the corresponding tables and you have replace the schema name with the schema name, under which you have created the corresponding tables (usually this is the dbo, if you haven't stated explicitly a schema name).

Upvotes: 1

Related Questions