how to count records depending on the condition? RRS feed

  • Question

  • Hello people, I need a help with the query that uses some smart counting(i think)


    Now I want write a query that maps each flightNum with FlightNumbering table and counts the number of NonRev, say the first flight i.e. id=1 and flightnum=122 falls under the range 1-150 and the nonRev value is 0 so now the NonRev is 1 now... so the idea is to see which number range the flight falls under and count the corresponding NonRev and Rev. Sometimes a flight may fall under two ranges like, Id 2 i.e 50, it falls under first and third flightnumbering range.

    Id FlightNum
    1 122
    2 50
    3 70
    4 200
    FlightNumbering(start int, End int, NonRev bit)
    Start	End  NonRev(bit)
    1  	50 	1
    51 	100 	0
    1  	150  	0
    151  	250  	1
    I hope I made myself clear enought. Hope to receive some help from the community. Cheers Sujan


    Sunday, September 19, 2010 10:43 AM


All replies

  • Hi ,

          Range is fixed or it is also passed as variable ? .. Please give us the required output of the data you are expecting ... 


    -- Reddy Balaji C. ##Mark as answer if it solves the issue
    Sunday, September 19, 2010 10:57 AM
  • No, it is not clear to me, please post sample data (CREATE TABLE.. INSERT INTO) and an expected result.. and please state what version  you are using
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, September 19, 2010 11:38 AM
  • @Reddy: Ranges are fixed and not passed as variables.

    @Uri: I am using SQL Server 2005 and I want to count the number of Non Revs(i.e. NonRev column has value 1) and Revs(i.e. NonRev column has value 0), so from the above give tables, my final result should look like this:

    Total Revs:

    NonRev(0) :3
    Rev(1) : 2


    Did you see how I got that result? Each FlightNum colum from Flight table is mapped to the FlightNumbering table, and based on which range it falls, I could the NonRev(i.e either 0 or 1)


    Sunday, September 19, 2010 9:52 PM
  • select NonRev,count(*) as Cnt from FlightNum F
    inner join FlightNumbering FN on F.FlightNum between FN.Start and Fn.[End]
    group by NonRev
    Monday, September 20, 2010 7:51 PM