The following is a script to create a table-valued UDF to calculate book sales by state. The UNION operator is used to get sales figures for more than one state:

USE pubs
GO
CREATE FUNCTION fnSalesByAuthorForState ( @State Char(2))
RETURNS TABLE
AS
RETURN (
SELECT TOP (2^32) [State]=@State, SSN = a.au_id, FirstName = a.au_fname,
LastName = a.au_lname, QtySold = SUM(s.qty),
Revenue = '$'+convert(varchar,sum(t.price*s.qty),1)
FROM Authors a
INNER JOIN TitleAuthor ta
ON a.au_id = ta.au_id
INNER JOIN Titles t
ON t.title_id = ta.title_id
INNER JOIN Sales s
ON s.title_id = t.title_id
INNER JOIN Stores st
ON st.Stor_ID = s.stor_id
WHERE st.state = @State
GROUP BY a.au_id, a.au_fname, a.au_lname
ORDER BY QtySold DESC, LastName, FirstName
)
GO

SELECT * from fnSalesByAuthorForState('CA')
UNION
SELECT * from fnSalesByAuthorForState('WA')
GO