number of occurences - 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

number of occurences

Author

Topic

olobraecky
Starting Member

5 Posts

Posted - 2008-05-13 : 13:48:55

Hi,

I'm hitting my head against the wall because of a really small issue...

Basically, let's assume I have the following:

BOOK_ID
========
1
2
2

in a table BOOK. I am trying to code a query that would return the book_id that appears the most in the book table.

Anyone knows how to do this?

The closest i've got is this:

select max(count(book_id)) from intermeeting group by book_id;

But this only returns the number of occurences of the record. What i'm interested in, is the ID of the record that occurs that many times...

Thanks for your help

olo

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-05-13 : 13:55:21

declare @t table (BOOK_ID int)
insert @t
          select 1
union all select 2
union all select 2
union all select 3
union all select 4
union all select 4

select top 1 with ties BOOK_ID, count(*) as cnt from @t group by BOOK_ID order by 2 desc

/*
BOOK_ID     cnt
----------- -----------
2           2
4           2
*/

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-13 : 13:57:02

[code]SELECT TOP 1 BOOK_ID,COUNT(*) AS Count
FROM Table
GROUP BY BOOK_ID
ORDER BY Count DESC[/code]

olobraecky
Starting Member

5 Posts

Posted - 2008-05-13 : 14:04:42

Hey guys, thanks for the quick replies. However, it doesn't seem to work... :s

BOOK_ID
=======
1
2
2

these are the records in the intermeeting table.

here is what i'm trying to return

BOOK_ID
=======
2

on the basis that the book with id = 2 appears twice in the table whereas book id = 1 appears only once.

I'm revising my exams and apparently, it can be done using max() and count()...

anymore ideas?

thanks,

olo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-13 : 14:06:27

quote:
Originally posted by olobraecky

Hey guys, thanks for the quick replies. However, it doesn't seem to work... :s

BOOK_ID
=======
1
2
2

these are the records in the intermeeting table.

here is what i'm trying to return

BOOK_ID
=======
2

on the basis that the book with id = 2 appears twice in the table whereas book id = 1 appears only once.

I'm revising my exams and apparently, it can be done using max() and count()...

anymore ideas?

thanks,

olo

why? what did you get?

olobraecky
Starting Member

5 Posts

Posted - 2008-05-13 : 14:11:06

My mistake, should have mentioned that I have to do it using max() and count() functions...

i know i got really close with:

select max(count(book_id)) from intermeeting group by book_id;

this outputs 2 since book_id = 2 occurs twice in the table...

My problem is that I can't find a way to output the book_ID of those occurences... :s

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-13 : 14:54:38

Or may be this:-

SELECT BOOK_ID AS Count
FROM intermeeting
GROUP BY BOOK_ID
HAVING COUNT(BOOK_ID)=(SELECT MAX(COUNT(BOOK_ID)
                 FROM intermeeting
                 GROUP BY BOOK_ID)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-13 : 15:09:06

Use a derived table.

SELECT MAX(Count) FROM (
SELECT COUNT(*) AS Count
FROM Table
GROUP BY BOOK_ID
) AS d

E 12°55'05.25"
N 56°04'39.16"

olobraecky
Starting Member

5 Posts

Posted - 2008-05-13 : 15:27:29

quote:

Originally posted by visakh16

Or may be this:-

SELECT BOOK_ID AS Count
FROM intermeeting
GROUP BY BOOK_ID
HAVING COUNT(BOOK_ID)=(SELECT MAX(COUNT(BOOK_ID)
                 FROM intermeeting
                 GROUP BY BOOK_ID)

tried this, but didn't work. I get:


SQL> select book_id as count from intermeeting group by book_id having count(book_id)=(select max(count(book_id) from intermeeting group by book_id);
select book_id as count from intermeeting group by book_id having count(book_id)=(select max(count(book_id) from intermeeting group by book_id)
                                                                                  *
ERROR at line 1:
ORA-00921: unexpected end of SQL command

olobraecky
Starting Member

5 Posts

Posted - 2008-05-13 : 15:33:30

quote:

Originally posted by olobraecky

quote:

Originally posted by visakh16

Or may be this:-

SELECT BOOK_ID AS Count
FROM intermeeting
GROUP BY BOOK_ID
HAVING COUNT(BOOK_ID)=(SELECT MAX(COUNT(BOOK_ID)
                 FROM intermeeting
                 GROUP BY BOOK_ID)

tried this, but didn't work. I get:


SQL> select book_id as count from intermeeting group by book_id having count(book_id)=(select max(count(book_id) from intermeeting group by book_id);
select book_id as count from intermeeting group by book_id having count(book_id)=(select max(count(book_id) from intermeeting group by book_id)
                                                                                  *
ERROR at line 1:
ORA-00921: unexpected end of SQL command

working!! thanks visakh. you just forgot a parenthesis... but it works real good.

SELECT BOOK_ID AS BookID
FROM intermeeting
GROUP BY BOOK_ID
HAVING COUNT(BOOK_ID)=(SELECT MAX(COUNT(BOOK_ID))
                 FROM intermeeting
                 GROUP BY BOOK_ID)

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-05-14 : 05:08:29

quote:

Originally posted by olobraecky

quote:

Originally posted by visakh16

Or may be this:-

SELECT BOOK_ID AS Count
FROM intermeeting
GROUP BY BOOK_ID
HAVING COUNT(BOOK_ID)=(SELECT MAX(COUNT(BOOK_ID)
                 FROM intermeeting
                 GROUP BY BOOK_ID)

tried this, but didn't work. I get:


SQL> select book_id as count from intermeeting group by book_id having count(book_id)=(select max(count(book_id) from intermeeting group by book_id);
select book_id as count from intermeeting group by book_id having count(book_id)=(select max(count(book_id) from intermeeting group by book_id)
                                                                                  *
ERROR at line 1:
ORA-00921: unexpected end of SQL command

...so you're using oracle? not that it's made a difference on this occasion but just to be aware this forum deals specifically with SQL Server

Em

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-05-14 : 05:10:02

quote:
Originally posted by senthilramtrs

try like this ....

select count(max(book_id)) from intermeeting group by book_id;

Regards,
Senthil Ram TRS

Did you test this at all?

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources