Pubs database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Pubs database

Author

Topic

tadin
Yak Posting Veteran

63 Posts

Posted - 2007-03-21 : 13:16:43

http://msdn2.microsoft.com/en-us/library/aa238305(SQL.80).aspx

how can i List the title name, year of order, state and total quantity for all sales?

Some of solutions are below but not for the above question.

-- list the titles by total sales price.
select
title,
sum(s.qty * t.price)
from titles t
join sales s on (s.title_id = t.title_id)
group by title
order by 2 desc

-- list the titles by total sales qty
select
t.title,
sum(s.qty)
from titles t
join sales s on (s.title_id = t.title_id)
group by t.title
order by 2 desc

-- list the stores and titles by sales price.
select
st.stor_name,
t.title,
sum(s.qty * t.price)
from titles t
join sales s on (s.title_id = t.title_id)
join stores st on (s.stor_id = st.stor_id)
group by
st.stor_name,
t.title
order by 1,3 desc

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-21 : 15:25:59

[code]SELECT t.title, year(sa.ord_date) AS [Year],
st.state, sum(sa.qty) AS TotalQuantity
FROM sales sa
INNER JOIN titles t ON t.title_id = sa.title_id
INNER JOIN stores st ON st.stor_id = sa.stor_id
GROUP BY t.title, year(sa.ord_date), st.state[/code]

tadin
Yak Posting Veteran

63 Posts

Posted - 2007-03-21 : 15:57:32

thanks. Since, i'm new to sql i get confused thanks for you great help.

tadin
Yak Posting Veteran

63 Posts

Posted - 2007-03-28 : 16:19:00

how can i show the number of orders and total quantity sold for each title. i used the inner join concept, but is there another approcach to this question.

SELECT t.title, year(sa.ord_date) AS [Year],
st.state, sum(sa.qty) AS TotalQuantity
FROM sales sa
INNER JOIN titles t ON t.title_id = sa.title_id
INNER JOIN stores st ON st.stor_id = sa.stor_id
where st.state ='ca'
GROUP BY t.title, year(sa.ord_date), st.state

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-28 : 16:23:39

quote:
Originally posted by tadin

how can i show the number of orders and total quantity sold for each title. i used the inner join concept, but is there another approcach to this question.

Why do you want to do it another way?

tadin
Yak Posting Veteran

63 Posts

Posted - 2007-03-29 : 15:22:35

This is how i used equi join i still get the same result. I just want to know the possibilities of approaching the same result and at the same time learning myself.

SELECT t.title, year(sa.ord_date) AS [Year],
st.state, sum(sa.qty) AS TotalQuantity
FROM sales sa,titles t, stores st
Where t.title_id = sa.title_id and st.stor_id = sa.stor_id
and st.state ='ca'
GROUP BY t.title, year(sa.ord_date), st.state

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-03-29 : 15:50:32

it's nice to know how to do things different ways, but that query will have the exact same query plan.

also that syntax is frowned on by some - it's not ANSI so it isn't portable.

often portability isn't very important in RDBMS though. Databases, and the platforms they run on, have a way of persisting...

www.elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources