Count the GROUP BY rows

A friend of mine asked me to solve his university#%92s exercise. In part of that exercise, I had to count a GROUP BY result rows.

create table book (
ISBN char(10),
Title char(20) not null,
category char(12),
primary key (ISBN)
);

Insert into book (isbn, title, category) values ('BN1001', 'Advanced SQL', 'Tech');
Insert into book (isbn, title, category) values ('BN1002', 'Harry Porter', 'Fiction');
Insert into book (isbn, title, category) values ('BN1003', 'Java Without Pain', 'Tech');
Insert into book (isbn, title, category) values ('BN1004', 'Rocky Mountains', 'Travel');
Insert into book (isbn, title, category) values ('BN1005', 'Banff', 'Travel');

The following query returns 3 rows.

SELECT Category
FROM Book
GROUP BY Category

But I need only the number 3 not three lines. I could do it by the following script but is there any other way?

SELECT Count( DISTINCT Category)
FROM Book
GROUP BY Category


CanadaDBA

Maybe he means

SELECT Count(*), Category
FROM Book
GROUP BY Category

Or simply

SELECT COUNT(DISTINCT Category) from Book

Oh! I am so sorry Luis. You are right, The script that solved my problem was as below:

SELECT Count(DISTINCT Category) FROM Book

quote:Originally posted by LuisMartin

I can't follow you.

Script:

SELECT Count( DISTINCT Category)
FROM Book
GROUP BY Category

Show:
1
1
1

no number 3.
Do you want number 3, because there is 3 differentes categories, or last output?

CanadaDBA

Yes, this was the script that solved my problem but now the question is that: is there any other way?

quote:Originally posted by Chappy

Or simply

SELECT COUNT(DISTINCT Category) from Book

CanadaDBA

There are a few way around but you still have to USE COUNT the one you have is the simpliest way why want more ? you are too GREEDY hehehhehe <img src='/community/emoticons/emotion-1.gif' alt='' /> <br /><br /><font color="blue">SELECT COUNT(*) AS Expr1<br />FROM (SELECT category<br /> FROM Book<br /> GROUP BY category) DERIVEDTBL</font id="blue"><br /><br /><font color="blue">SELECT COUNT(*) AS Expr1<br />FROM (SELECT DISTINCT category<br /> FROM Book)</font id="blue"><br /><br /><br /> <br />Since COUNT return the number of ROWS in a query and there is NO OTHER WAY EXCEPT USING IT hehhee the rest is just a twist around.<br /><br />May the best cheaters win

ha ha ha! <img src='/community/emoticons/emotion-1.gif' alt='' /> <br />I am agree with you that my script is simple and good enough but the reason that I wrote this article was that I put much time to get the result from <br /><font color="green"><br />SELECT Count(SELECT DISTINCT category FROM Book) <br /></font id="green"><br />and I was not able to find out where is my mistake. Your script showed the right way. Thanks!<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by tdong</i><br /><br />There are a few way around but you still have to USE COUNT the one you have is the simpliest way why want more ? you are too GREEDY hehehhehe <img src='/community/emoticons/emotion-1.gif' alt='' /> <br /><br /><font color="blue">SELECT COUNT(*) AS Expr1<br />FROM (SELECT category<br /> FROM Book<br /> GROUP BY category) DERIVEDTBL</font id="blue"><br /><br /><font color="blue">SELECT COUNT(*) AS Expr1<br />FROM (SELECT DISTINCT category<br /> FROM Book)</font id="blue"><br /><br /><br /> <br />Since COUNT return the number of ROWS in a query and there is NO OTHER WAY EXCEPT USING IT hehhee the rest is just a twist around.<br /><br />May the best cheaters win<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />CanadaDBA