find the nth max of sum(count(column))

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 2005 Forums

Transact-SQL (2005)

find the nth max of sum(count(column))

Author

Topic

mvssrikanth1
Starting Member

4 Posts

Posted - 2007-12-24 : 06:44:48

i have a table "a" with field "id".

I have another table "b" with fields "catid" and "id".I have to find the nth maximum of categories according to the sum(count(id)).

for example table "a" has field "id" with values "1,1,2,2,2,2,3,3,3,4,5,5,5,5".

Table "b" has values for catid and id are as follows.

catid id

1 1
1 2
2 3
2 4
2 5

I have to find the nth maximum of categories according to sum(count(id)).

first i have to calculate count(id) i.e. count(1)=2,count(2)=4,count(3)=3,count(4)=1,count(5)=4

Then catid" 1 " has sum(count(id)) as count(1)+count(2)=6

catid"2" has sum(count(id)) as count(3)+count(4)+count(5)=8

cat"2" is the maximum and catid"1" is minimum.

I have to write t-sql query for this problem.
please help me in this regard.
Thankyou
mvssrikanth

pootle_flump

1064 Posts

Posted - 2007-12-24 : 07:51:57

Hi

You don't look like you need a sum - just join the tables and COUNT(*), grouping by catid. BTW - where does the "nth max" come in? Do you just need to know which catid has the largest count? If so - select top 1 and order by the COUNT(*) DESC.

HTH

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2007-12-24 : 08:03:56

Or if you need something else, please write the input data as well as your desired output results.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-24 : 09:56:04

try this:-

SELECT temp.catid 
FROM
(
  SELECT ROW_NUMBER() OVER(ORDER BY t.RecCount DESC) as 'RowNo',
         t.catid
  FROM
  (    SELECT b.catid,COUNT(b.catid) AS 'RecCount' 
       FROM TableA a
       INNER JOIN TableB b
       ON a.id=b.id
       GROUP BY b.catid
   ) t
)temp
WHERE temp.RowNo=@n

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources