Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2002
    Posts
    74

    Unanswered: Count of Counts in SQL Server

    Hi folks,

    I have the following query to run:

    select count(
    select count(*)
    from student
    group by firstName
    having count(*) > 1)

    Basically, I first need to get a count of all students that have the same first name, then I need total count of all those students. How can I change the above query to get the result I need?

    Thanks so much!

    -Parul

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that doesn't make any sense

    a count of a count will always be 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2002
    Posts
    74
    why is that so? how do we get a count of all those rows that appear in the inner query?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, let's break this problem down into steps

    could you please run the inner query all by itself and show me what you get
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2002
    Posts
    74
    Since the inner select query has a group by field, the number of rows it returned may be more than one also.

  6. #6
    Join Date
    Oct 2002
    Posts
    74
    it shows you different counts by student first name; the result looks like:
    count
    ------
    5
    24
    2
    23
    2

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ah yes, okay, then i think what you want is this --
    Code:
    select count(*)
      from (
           select count(*)
             from student
           group by firstName
           having count(*) > 1
           ) as counts
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Oct 2002
    Posts
    74
    Exactly, but this is giving me the following error:

    No column was specified for column 1 of 'counts'.

  9. #9
    Join Date
    Oct 2002
    Posts
    74
    I got it! The inner select column needs an alias.
    select count(*)
    from (
    select count(*) as 'cnt'
    from student
    group by firstName
    having count(*) > 1
    ) as counts

    Thanks so much for your help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •