Results 1 to 6 of 6

Thread: cout() issue

  1. #1
    Join Date
    Jan 2009
    Posts
    124

    Unanswered: cout() issue

    In this query I want the recordset to be empty if the count is more than ten. Other wise I want to know who many the count is.

    SELECT pre_28_reg, count(*) AS count
    FROM Meet_reg
    WHERE pre_28_reg =2 AND count < 10
    GROUP BY pre_28_reg

    Nick

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by oldnickj
    In this query I want the recordset to be empty if the count is more than ten. Other wise I want to know who many the count is.
    This should work:
    Code:
    SELECT pre_28_reg, count(*) AS count
    FROM Meet_reg
    WHERE pre_28_reg = 2 
    GROUP BY pre_28_reg
    having count(*) <= 10
    I can't see why you are selecting pre_28_reg as you know the value will be 2 but I left it in. I changed the < 10 to be <= 10 as in your description.

    Mike

  3. #3
    Join Date
    Jan 2009
    Posts
    124

    Count()

    Tanks Mike, it works great. I went for a long walk and realized I need to use have or max. Anything that looks simple isn't!

    nick

  4. #4
    Join Date
    Dec 2008
    Posts
    135
    r u can use derived query
    but prefer mike_bike_kite query than this one............

    select * from (SELECT pre_28_reg, count(*) AS count
    FROM Meet_reg
    WHERE pre_28_reg =2
    GROUP BY pre_28_reg)s where count < 10

  5. #5
    Join Date
    Jan 2009
    Posts
    124

    COunt()

    Interesting, so the alias for the derived doesn't need an AS?..thanks!

    Nick

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    best practice is always to code AS, even though it is optional

    (oracle developers will argue the opposite, since oracle, for some inexplicable reason, doesn't support AS)

    here's a good example...

    how many columns does this query return?
    Code:
    SELECT frabilgimjer,
           quistipunctous,
           scrimflabat
           horbliston,
           haplistplogget,
           opsilfrummer,
           whipfintaggle
      FROM ...
    coding style matters!!!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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