Summing values in discrete ranges

Summing values in discrete ranges

I have a table that, among other columns, contain the columns:

id char(6) not null,
value double not null

I need to create a query that will count entries per value range grouped by id, something like:

id       <1.0     <2.0     <3.0     <4.0 
-------  -----    -----    -----    ----
AA00011    2        5        14      8
AA00239    3        1        23      5
AB00005    6        3        13      2
....

This database is in MySQL 4.0, which supports only limited subqueries in the where clause and does not have views. Is there any way to write this query in SQL?


    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

Yes, there is. It's not always possible to work around the lack of support for subqueries, but in this case you can. In fact, I would do it this way even if subqueries were supported:

select id
     , sum( case when value < 1.0 
                 then 1
                 else 0  end )     as "<1.0"
     , sum( case when value >= 1.0 
                  and value < 2.0 
                 then 1
                 else 0  end )     as "<2.0"
     , sum( case when value >= 2.0 
                  and value < 3.0 
                 then 1
                 else 0  end )     as "<3.0"
     , sum( case when value >= 3.0 
                  and value < 4.0 
                 then 1
                 else 0  end )     as "<4.0"
  from thetable

Note that BETWEEN would not be right in this situation, as it includes the end points of the ranges.

For More Information


This was first published in September 2003

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top