Forum Stats

  • 3,770,505 Users
  • 2,253,129 Discussions
  • 7,875,493 Comments

Discussions

Help in Query

Rajnish Chauhan
Rajnish Chauhan Member Posts: 470 Blue Ribbon

Hi Friends,

i have Table like student , please help to create perfect Query to get 'Expected Result' in picture.



Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond
    edited Jun 29, 2021 10:11AM

    Hi, @Rajnish Chauhan

    Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of how you get those results from that data.

    If you can show what the problem is using commonly available tables (like the tables in the Oracle-supplied SCOTT schema) then you don't need to post any sample data; just the results and explanation.

    Always say which version of Oracle you're using (e.g. 12.2.0.1.0).

    You can use GROUP BY and COUNT to get the results you want. The tricky thing is what to GROUP BY. If each group represents the same number of years, then you can use a rounding function (like CEIL, below, FLOOR, ROUND or TRUNC). Since I don't have a copy of your table, I used the scott.emp table to illustrate:

    SELECT   TO_CHAR ((500 * CEIL (sal / 500)) - 499)
          || ' - '
          || TO_CHAR ( 500 * CEIL (sal / 500)) AS sal_range
    ,        COUNT (*) 	    	   	   AS num_of_emps
    FROM	 scott.emp
    GROUP BY CEIL (sal / 500)
    ORDER BY CEIL (sal / 500)
    ;
    

    Output:

    SAL_RANGE    NUM_OF_EMPS
    ------------ -----------
    501 - 1000       2
    1001 - 1500      5
    1501 - 2000      1
    2001 - 2500      1
    2501 - 3000      4
    4501 - 5000      1
    

    Instead of displaying a range (like '501 - 1000') in one VARCHAR2 column, you might want to display the range in two NUMBER columns.

    If different groups represent different numbers of years (for instance, one group covers 6 years, but the others cover 5 years), then you can GROUP BY a CASE expression, or else join to a table (or result set) like the scott.salgrade table.

    Rajnish Chauhan
  • EdStevens
    EdStevens Member Posts: 28,536 Gold Crown

    Well, to start with, your data model is seriously flawed. You should not be storing 'age'. Age is constantly increasing.

    Suppose Ram had a birthday between the time this data was entered and the time you run your report.


    You should be storing date of birth, as data type DATE, then use that to make any "age" calculations at run time.

    Pauliejflack
  • jflack
    jflack Member Posts: 1,516 Bronze Trophy

    I agree with @EdStevens but given the data as you provided it, the query is:

    WITH student_groups AS (
      SELECT student_name,
             CASE
               WHEN age between 0  and 5  then '0-5'
               WHEN age between 6  and 10 then '6-10'
               WHEN age between 11 and 15 then '11-15'
               WHEN age between 16 and 20 then '16-20'
               ELSE 'other'
             END age_group
       FROM student
      )
    SELECT age_group, count(*) as "#Students"
      FROM student_groups
    GROUP BY age_group
    
    Rajnish Chauhan
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,926 Red Diamond
    edited Jun 30, 2021 3:33PM

    CASE statement WHEN/ELSE clause is evaluated only when previous WHEN clause condition didn't produce TRUE (short-circuit evaluation). Therefore there is no need for BETWEEN:

             CASE
               WHEN age <= 5  then '0-5'
               WHEN age <= 10 then '6-10'
               WHEN age <= 15 then '11-15'
               WHEN age <= 20 then '16-20'
               ELSE 'other'
             END age_group
    

    SY.