Results 1 to 6 of 6

Thread: SQL Histogram

  1. #1
    Join Date
    Jun 2009
    Posts
    3

    Unanswered: SQL Histogram

    Dear All,

    Im new to SQL and I hope you will help me in resolving the following problem. I need to write a query which groups students by age_group, gender, and name of the school. Such that the each age_group is broken down into male and female against the name of each school. Thus,

    School1 | Age Group1 ( Female, Male)| Age Group2 ( Female, Male)|..
    School2 | Age Group1 ( Female, Male)| Age Group2 ( Female, Male)|..
    School3 | Age Group1 ( Female, Male)| Age Group2 ( Female, Male)|..

    I hope that makes sense.

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You are going to have a problem in that your dataset already has there dimensions (school, age group, and sex), and you haven't even specified what values you want to aggregate yet. So you are going to need to decide how you want this n-dimensional recordset displayed on a two-dimensional report.

    But in general, it is best to let the database do the aggregation for you, but let your reporting tool actually pivot and format the data for display.

    What is the DDL for your table? What are you going to use to display the report?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2009
    Posts
    3
    Thanks Blindman,

    Table1
    studentName varchar, school varchar, age int, gender varchar

    In our report we need to group the ages using the following ranges;
    0-4, 5-10, 11-16

    I am using the report for educational purposes. I am still new to SQL and I'm trying to find ways in which to design a histogram in SQL.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Who are you trying to educate? Blindman is quite right - you should avoid using SQL to construct columns in resultsets that are based on the data from a single column. Excel, reporting services, Access, .NET are all better for this.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The "Best Practices" principle is:
    Use the database to collect and return the data.
    Use the interface to format and display the data.
    "Pivoting" the data, which is what you are trying to do, is easily handled within Excel, Crystal Reports, etc. Arguably, the grouping into 5 year age buckets could also be handled by your reporting interface, but might also be appropriate for the database to do in order to avoid returning extremely large result sets.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jun 2009
    Posts
    3
    Thanks a million.
    Cheers!

Posting Permissions

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