SQL Server Performance

Count the GROUP BY rows

Discussion in 'General Developer Questions' started by CanadaDBA, Jul 12, 2004.

  1. CanadaDBA New Member

    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
  2. Luis Martin Moderator

    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?



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  3. bambola New Member

    Maybe he means

    SELECT Count(*), Category
    FROM Book
    GROUP BY Category
  4. Chappy New Member

    Or simply

    SELECT COUNT(DISTINCT Category) from Book
  5. CanadaDBA New Member

    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
  6. CanadaDBA New Member

    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
  7. tdong New Member

    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
  8. CanadaDBA New Member

    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

Share This Page