I'm not sure I did this right. It's for an assignment for school where i'm supposed to return the author last name and the balance (described below) relating to each author. It's from the pubs database (I'm thinking that most people have used it for testing/learning before?)
I'm an SQL noob, leave me alone. :P
( royalty amount - advance ) , where royalty amount is calculated as a percentage of gross sales, ie:
royalty amount = year-to-date sales * price * royalty/100
Here's the code. I get a result set but it doesn't seem right, some values are in the hundreds of thousands. Although I suppose it's possible for an author to make that much...it's not that likely.
select au_lname 'Last Name', sum((ytd_sales * price * (royaltyper / 100)) - advance) 'Balance'
from authors a
join titleauthor ta
on a.au_id = ta.au_id
join titles t
on t.title_id = ta.title_id
group by (a.au_lname)Does this look right? Lol...