How to sum/group/sort columns in a view

Question

I have two tables, Author_Dim and Author_Fact as below:

CREATE TABLE Author_Dim
(
TitleAuthor_ID INT IDENTITY(1,1),
Title_ID CHAR(20),
Title VARCHAR(80),
Type_Title CHAR(12),
Author_ID CHAR(20),
Last_Name   VARCHAR(40),
First_Name VARCHAR(20),
Contract_Author BIT,
Author_Order INT,
PRIMARY KEY (TitleAuthor_ID),
);
GO

CREATE TABLE Author_Fact
(
Fact_ID INT IDENTITY(1,1),
TitleAuthor_ID INT,
Author_ID CHAR (20),
Price DEC(10,2),
YTD_Sales INT,
Advance DEC(10,2),
Royalty INT,
Royalty_Perc INT,
Total_Sales DEC(10,2),
Total_Advance DEC(10,2),
Total_Royalty DEC(10,2)
PRIMARY KEY(Fact_ID),
FOREIGN KEY (TitleAuthor_ID) REFERENCES Author_Dim(TitleAuthor_ID),
);
go

I wish to create a view that gives the total royalties paid per author and then sorts it with the highest paid author shown first, i.e. it sums the Total_Royalty column, groups it by the Author_ID and then sorts the Total_Royalty in descending order.

I have the below but I'm not sure how to add the sum/group/sort functions to the view:

Create view [Total_Royalty_View] As (
Select Author_Dim.Author_ID, Author_Dim.Last_Name, Author_Dim.First_Name, Author_Fact.Total_Royalty
From Author_Dim
Join Author_Fact
On Author_Fact.TitleAuthor_ID = Author_Dim.TitleAuthor_ID
);
Go

Did you check [this](https://stackoverflow.com/questions/15187676/create-a-view-with-order-by-clause) question? Order by is ignored if you're just selecting from a table you need to specify it during the select query. — DerMaddi, Feb 28 '21 at 11:12

score 2 · Accepted Answer · answered Feb 28 '21 at 11:14

In SQL it is all tables. You select from tables and the result is again a table (consisting of columns and rows). You can store a select statement for re-use and this is called a view. You could just as well write an ad-hoc view (a subquery in a from clause). Their results are again tables.

And tables are considered unordered sets of data.

So, you cannot write a view that produces an ordered set of rows.

Here is the view (unordered):

create view total_royalty_view as 
select
  a.author_id,
  a.last_name,
  a.first_name,
  coalesce(r.sum_total_royalty, 0) as total_royalty
from author_dim a
left join
(
  select titleauthor_id, sum(total_royalty) as sum_total_royalty
  from author_fact
  group by titleauthor_id
) r on r.titleauthor_id = a.titleauthor_id;

And here is how to select from it:

select *
from total_royalty_view
order by total_royalty desc;

How to sum/group/sort columns in a view

1 Answers1