Results 1 to 4 of 4
  1. #1
    Join Date
    May 2012
    Posts
    16

    Smile Unanswered: How to count the number of selected records and store the result to another column?

    Code:
    SELECT ((ACOS(SIN(34.37769 * PI() / 180) * SIN(latitude * PI() / 180) 
    + COS(34.37769 * PI() / 180) * COS(latitude * PI() / 180) * COS((132.404738 - longitude) 
    * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS ranges FROM completeyuubinwithlatlon having ranges<=3
    First, I have that code whenever i disregard the "having ranges<=3" it selects record but when I include it it returns error. The problem is I want to just select records with ranges less than 3 miles..how do I do that.. and I want to count the number of selected rows and store it on a column on the same table.

    Please help me. Thanks.

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Try for selecting:

    Code:
    with CTE as
    (
        SELECT ( ( ACOS( SIN(34.37769 * PI() / 180) * 
                         SIN(latitude * PI() / 180) + 
                         COS(34.37769 * PI() / 180) * 
                         COS(latitude * PI() / 180) * 
                         COS((132.404738 - longitude) * PI() / 180) 
                       ) * 180 / PI() 
                 ) * 60 * 1.1515 
               ) AS ranges 
        FROM completeyuubinwithlatlon 
    )
    
    select * from CTE 
    where ranges <= 3
    To store the number of selected rows would necessary to verify the table structure, but could also be used the CTE.

    Hope this helps.

  3. #3
    Join Date
    May 2012
    Posts
    16
    Thank You very much!

  4. #4
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Please notice how much more readable the reply is compared to the original post.

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
  •