Complicated Query - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Complicated Query

Author

Topic

ABrown
Starting Member

9 Posts

Posted - 2005-04-12 : 00:24:41

I'm working with the Northwind database in SQL Server. I need to write a query that displays for each product, the number of days in 1997 with 0 orders, 1 order, and > 1 order. I easily joined the tables and started counting but don't know how to roll it up. Any ideas?

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-04-12 : 01:30:25

Hey Brownie -

Is this by any chance an assignment question?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-04-12 : 02:28:53

I don't have the northwind database, so I'm guessing about the tables, but how about this:

select prodid, count(zero) as zeroes, count(one) as ones, count(more) as mores
from
	(select prodid, 
		zero = case when countperday = 0 then orderdate end,
		one = case when countperday = 1 then orderdate end,
		more = case when countperday > 1 then orderdate end
	from
		(select o.prodid, od.orderdate, count(*) as countperday
		from orders o 
			inner join orderdetails od
			on od.orderid = o.orderid
		where year(od.orderdate) = 1997
		group by o.prodid, od.orderdate) x
) y
group by prodid

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

ABrown
Starting Member

9 Posts

Posted - 2005-04-12 : 12:55:55

Aussie,

This is not for a class project. This looks great, except for those days in 1997 without an order, it is returning zeros. Is there any way to count the number of days with no orders. The rest looks good so far.

select productid, count(zero) as zeroes, count(one) as ones, count(more) as mores
from
(select productid,
zero = case when countperday = 0 then orderdate end,
one = case when countperday = 1 then orderdate end,
more = case when countperday > 1 then orderdate end
from
(select od.productid, o.orderdate, count(*) as countperday
from orders o
inner join [order details] od
on od.orderid = o.orderid
where year(o.orderdate) = 1997
group by od.productid, o.orderdate) x
) y
group by productid

ABrown
Starting Member

9 Posts

Posted - 2005-04-12 : 16:34:58

Aussie,

What do you think about this? I'm still checking the null values and possible duplicates to see if I'm getting the correct number of days with no orders?

drop table #temp1
select productid, count(zero) as [No Orders], count(one) as [One Order], count(more) as [> One Order]
into #temp1
from
(select productid,
zero = case when countperday = 0 then orderdate end,
one = case when countperday = 1 then orderdate end,
more = case when countperday > 1 then orderdate end
from
(select p.productid, o.orderdate, count(*) as countperday
from orders o
inner join [order details] p
on p.orderid = o.orderid
where year(o.orderdate) = 1997
group by p.productid, o.orderdate) x
) y
group by productid

select productid, [One Order], [> One Order], 365-([One Order] +
[> One Order]) as [No Orders]
from #temp1

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-04-12 : 18:59:35

Mr Brown (my last name too)

Sorry, took a while to get back - its been evening in Australia. Glad to hear its not a class project, and sorry for suggesting it!

Your suggestion looks fine - sorry i didn't quite get what you were asking...

why not do it in one query without the temp table?

select productid, count(one) as [One Order], count(more) as [> One Order],
 365 - (count(one)+count(more)) as [No Orders]
from
(select productid,
one = case when countperday = 1 then orderdate end,
more = case when countperday > 1 then orderdate end
from
	(select p.productid, o.orderdate, count(*) as countperday
	from orders o
	inner join [order details] p
	on p.orderid = o.orderid
	where year(o.orderdate) = 1997
	group by p.productid, o.orderdate) x
	) y
group by productid

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

ABrown
Starting Member

9 Posts

Posted - 2005-04-13 : 12:11:45

Mr. Brown,
You are very good! Can you explain what role the x and y serve? Also, can you suggest a good book to learn more about techniques like this?

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2005-04-13 : 12:52:45

X and Y are the derived table aliases. They are required when using a select query in the from clause. You can also use table aliases when not doing a select query in the from clause, for example.

SELECT a.blah, b.blah
FROM MyTable a INNER JOIN MySecondTable b ON (a.key = b.key)

To learn more read books on online!

Dustin Michaels

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-04-13 : 20:17:55

Sorry Brownie - night time in Oz gets me again.

Thanks Dustin -

As for a good book - robvolk has one that he recommends - I think its called the Guru's guide to SQL or something look here. Personally, I reckon the best way to learn is to stay tuned to this site, and ask questions.

Of course, look up aliases in Books On Line (BOL) won't hurt either...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

ABrown
Starting Member

9 Posts

Posted - 2005-04-14 : 18:36:39

I've used aliases before but did not know to use them when a select clause comprises the from statement. Very interesting! I will check out the book you recommended. Now that I know guys of your ilk hang out here, I'll be back! You did a great service. Thanks again.

ABrown
Starting Member

9 Posts

Posted - 2005-04-15 : 17:19:08

Hey Aussie,
Can you generalize about when one would use a select clause in the from statement (for certain types of queries) versus when to use a select clause in the where statement? Thanks for any insights into this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources