Tricky row count - 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)

Tricky row count

Author

Topic

james.halligan@gmail.com
Starting Member

1 Post

Posted - 2005-09-14 : 18:16:49

Folks,

(I have used the 'pubs' database)

I'm looking to modify the SQL below to develop a derived table with a row count. At the moment it assigned a counter for every record in the 'sales' table regardless of any filter in the where clause. Can anyone help me get the counter to only include those records as specified by the where clause. You see the row counter starts at 4 because there are 3 records where order <'6871'

I have used the 'pubs' database from SQL server

use pubs
select a1.ord_num,count(a2.ord_num)as counter
from(select sales.ord_num,count(sales.ord_num)as jcount from sales group by sales.ord_num)a1,
(select sales.ord_num,count(sales.ord_num)as jcount from sales group by sales.ord_num)a2
where a1.ord_num>=a2.ord_num and a1.ord_num >'6871'
group by a1.ord_num, a1.jcount

this would really dig me out of a hole, so thanks in advance

regards

James

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-09-14 : 20:09:21

hi james -

I think you can solve your problem by joining to one of your subqueries, but not sure because I don't have the pubs database here

could you post some example data and a row or two of the result you want?

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

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-09-14 : 20:44:45

How about this:


select a1.ord_num, count(a2.ord_num) as counter
from 
	(select sales.ord_num, count(sales.ord_num) as jcount 
	from sales 
	where ord_num > '6871'
	group by sales.ord_num) a1,

	(select sales.ord_num, count(sales.ord_num) as jcount 
	from sales 
	where ord_num > '6871'
	group by sales.ord_num) a2

where a1.ord_num >= a2.ord_num 
group by a1.ord_num, a1.jcount

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources