Recent TOP values with count/group duplicates

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)

Recent TOP values with count/group duplicates

Author

Topic

mem
Starting Member

28 Posts

Posted - 2005-06-10 : 15:04:54

Hello,

I need to count the most recent top 10 values in a table with a count and group by of the duplicates. I've tried a couple of things with no luck.

What i have so far...

SELECT TOP 10 Order_Date, Order_Number, MTN, Count(Order_Number) as DupCount
FROM tblTransactions
WHERE Order_Date = somedate
GROUP BY Order_Number, Order_Date, MTN, Transaction_ID
ORDER BY Transaction_ID DESC

The results:

Order_Date Order_Number MTN DupCount
------------------------ ------------ ------------------- -----------
2005-06-08 00:00:00.000 1245614777 9156445546 1
2005-06-08 00:00:00.000 5545946144 8855451343 1
2005-06-08 00:00:00.000 9995566772 9165551515 1
2005-06-08 00:00:00.000 1321231534 5564655654 1
2005-06-08 00:00:00.000 1111111111 1111111111 1
2005-06-09 00:00:00.000 5555555555 5555555555 1
2005-06-09 00:00:00.000 5555555555 8885559955 1
2005-06-08 00:00:00.000 1313154654 9956564564 1
2005-06-09 00:00:00.000 3131312313 9999999999 1

What I need:

Order_Date Order_Number MTN DupCount
------------------------ ------------ ------------------- -----------
2005-06-08 00:00:00.000 1245614777 9156445546 1
2005-06-08 00:00:00.000 5545946144 8855451343 1
2005-06-08 00:00:00.000 9995566772 9165551515 1
2005-06-08 00:00:00.000 1321231534 5564655654 1
2005-06-08 00:00:00.000 1111111111 1111111111 1
2005-06-09 00:00:00.000 5555555555 5555555555 2
2005-06-08 00:00:00.000 1313154654 9956564564 1
2005-06-09 00:00:00.000 3131312313 9999999999 1

Thanks in advance.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-10 : 15:16:37

Does this do it?

SELECT TOP 10 Order_Date, Order_Number, min(MTN) minMTN, Count(Order_Number) as DupCount
FROM tblTransactions
WHERE Order_Date = somedate
GROUP BY Order_Number, Order_Date, Transaction_ID
ORDER BY Transaction_ID DESC

btw- if order_date is datetime use this instead:
WHERE Order_Date BETWEEN dateadd(month, datediff(month,0,getdate()),0) AND getdate()

Be One with the Optimizer
TG

mem
Starting Member

28 Posts

Posted - 2005-06-10 : 17:14:04

Thanks for the help, but that did not work. Any more suggestions?

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-06-10 : 19:46:39

I take it your transaction_id is unique? Grouping by that will prevent the count you are looking for. See below:

declare @tblTrans table(transaction_id int identity(1,1),order_date datetime, order_number varchar(10), mtn varchar(10))
insert into @tblTrans 
	select '2005-06-08 00:00:00.000', 1245614777, 9156445546 union
	select '2005-06-08 00:00:00.000', 5545946144, 8855451343 union
	select '2005-06-08 00:00:00.000', 9995566772, 9165551515 union
	select '2005-06-08 00:00:00.000', 1321231534, 5564655654 union
	select '2005-06-08 00:00:00.000', 1111111111, 1111111111 union
	select '2005-06-08 00:00:00.000', 5555555555, 5555555555 union
	select '2005-06-08 00:00:00.000', 5555555555, 8885559955 union
	select '2005-06-08 00:00:00.000', 1313154654, 9956564564 union
	select '2005-06-08 00:00:00.000', 3131312313, 9999999999

SELECT TOP 10 Order_Date, Order_Number, min(MTN) minMTN, Count(mtn) as DupCount
FROM @tblTrans
WHERE Order_Date = '2005-06-08 00:00:00.000'
GROUP BY Order_Number, Order_Date --, Transaction_ID
-- ORDER BY transaction_id


-- results

Order_Date                                             Order_Number minMTN     DupCount    
------------------------------------------------------ ------------ ---------- ----------- 
2005-06-08 00:00:00.000                                1111111111   1111111111 1
2005-06-08 00:00:00.000                                1245614777   9156445546 1
2005-06-08 00:00:00.000                                1313154654   9956564564 1
2005-06-08 00:00:00.000                                1321231534   5564655654 1
2005-06-08 00:00:00.000                                3131312313   9999999999 1
2005-06-08 00:00:00.000                                5545946144   8855451343 1
2005-06-08 00:00:00.000                                5555555555   5555555555 2
2005-06-08 00:00:00.000                                9995566772   9165551515 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources