subquery  cnt>x

Posted on 2011-04-23
Last Modified: 2012-05-11
If I have a query like:  
select MyText, cnt=(count(*) FROM TableB where TableAId=a.TableAId) FROM TableA a
and I would only like to select those rows where cnt>5. How do I do that?

Question by:johnkainn
    1 Comment
    LVL 142

    Accepted Solution

    this should do:
    ;with data as (
    select MyText, cnt=(select count(*) FROM TableB where TableAId=a.TableAId) 
      FROM TableA a
    select * from data 
     where cnt > 5

    Open in new window


    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now