Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    11
    Provided Answers: 1

    Question Unanswered: Selecting all records where column count is greater than 1

    Hi guys....
    I'm trying to figure out the SQL for selecting all records where column count is greater than 1 from a table. So using the following data:

    Goal:
    To select all ItemNames from the ItemTable where the ItemCategory count of other items with the same category is greater than 1


    ItemTable
    ----------------
    ID-Integer
    ItemName-Varchar2
    ItemCategory-Varchar2

    ID|ItemName|ItemCategory
    ------------------------
    1 |Apple |A
    2 |Orange |C
    3 |Banana |A
    4 |Grape |B
    5 |Cherry |A
    6 |Lemon |B
    7 |Melon |D

    Desired Result Set:

    ItemName|ItemCategory
    ----------------------------
    Apple |A
    Banana |A
    Grape |B
    Cherry |A
    Lemon |B

    Thank you!!!
    Tony

  2. #2
    Join Date
    Mar 2004
    Posts
    11
    Provided Answers: 1
    I figured it out:
    select ItemName,ItemCategory from ItemTable
    where ItemCategory in (
    select ItemCategory
    from ItemTable
    group by ItemCategory
    having count(*) > 1
    )

    Tony

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
  •