Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder Stooshie's Avatar
    Join Date
    Mar 2008
    Location
    Dundee, Scotland
    Posts
    380
    Thanks
    9
    Thanked 39 Times in 39 Posts

    Select from table where count == N for each distinct row

    Hi, I'll try and make this as clear as I can.

    If I have a table (t) with a column (c) How do I display a list of c values where that value only exists once in the table.

    I have tried:

    Code:
    SELECT
    	t.c,
    	count(t.c) AS num
    FROM
    	t
    GROUP BY
    	t.c
    WHERE
    	num = 1
    but I receive an error message saying the WHERE is an invalid use of the GROUP BY clause.

    Any ideas anyone?

    Andrew.
    Regards, Stooshie
    O

  • #2
    Regular Coder Stooshie's Avatar
    Join Date
    Mar 2008
    Location
    Dundee, Scotland
    Posts
    380
    Thanks
    9
    Thanked 39 Times in 39 Posts
    It's OK, I've got it.

    Code:
    SELECT
    	t.c,
    	count(t.c) AS num
    FROM
    	t
    GROUP BY
    	t.c
    HAVING
    	count(t.c) = 1
    I had totally forgotten about the HAVING clause. :-)
    Regards, Stooshie
    O


  •  

    Tags for this Thread

    Posting Permissions

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