February 5, 2004 at 11:26 am
I am using SQL server 2000.
I need a query to return a count of records with unique combinations of two fields, book and page.So I wrote the following query, but it will not let me print the book field to see which book, the page belongs to. Is there any other way of doing this ?
select distinct( page )
from tl10_land where book in (select distinct(book) from tl10_land)
thanks in advance.
February 5, 2004 at 11:39 am
You may want to consider using the GROUP BY clause in your SELECT statement. GROUP BY will group the data in groups as desired. Look up the GROUP BY clause in "Books On Line" (BOL) for more information.
select [page], [book], count(*)
from tl10_land
group by [page], [book]
Cheers!
Billy
February 5, 2004 at 11:43 am
I'm not sure I understand what you are trying to do. The query you have will return a list of unique [page] for all of the records since by definition all records meet the condition you give (look at it for five minutes and you will see what I mean).
select page, book, count(*) as CountRecords
from tl10_land
group by page, book
This gives a count of the records with the page book combination.
I think what you want is:
select distinct page, book
from tl10_land tl
where 1 = (select count(* ) from tl10_land tlsub where tl.page = tlsub.page and tl.book = tlsub.boo)
Russel Loski, MCSE Business Intelligence, Data Platform
February 5, 2004 at 12:07 pm
A section of my data will look like this:
book | page .........other fields
1 2
2 1
2 3
2 2
1 1
1 2
3 1
2 3
You will notice that the combination 1 ,1 appears twice in the table .But I want this to be counted only once.
Basically I need a count of all the unique pages in a book with the book, page and count output.
Any help will be appreciated. Thanks for your responses.
February 6, 2004 at 5:17 am
just m 2ct:
select book, count(distinct page) as pagecount
from tl10_land
group by book
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 6, 2004 at 5:28 am
thanku....works well.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply