1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to use an identifier in sql statement

Discussion in 'SQL PL/SQL' started by mukulverma2408, Jul 15, 2015.

  1. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    38
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    Hi,

    Is it possible to use an identifier of my sql statement in same statement with some mathematical operation somewhat like given below :
    Code (Text):

    select count(1) cnt, sum(marks_obtained) mkobt, rollno,CNT*100 from score group by rollno;
     
    My table data is as below :
    Code (Text):

    SQL> select * from score;

    SUBJECT                            ROLLNO MARKS_OBTAINED
    ------------------------------ ---------- --------------
    physics                                 1             40
    chemistry                               1             60
    maths                                   1             70
    science                                 1             80
    maths                                   2             30
    physics                                 2             20
    physics                                 3             50
    maths                                   4             20

    8 rows selected.
     
     
  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    No -- or at least not in the fashion you attempted. You can perform the aggregation in a subquery and then a secondary calculation using the aggregated value in a parent query. For example:

    Code (Text):

    SELECT cnt, mkobt, rollno, cnt*100 AS cnt_x100
    FROM   (SELECT COUNT(1) cnt,
                   SUM(marks_obtained) mkobt,
                   rollno
            FROM   score
            GROUP BY rollno);
     
     
    mukulverma2408 likes this.
  3. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    With?
     
  4. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    You can't just stop with 'With?' without providing a WITH example. OP's obviously a SQL newbie.

    Code (Text):

    WITH scgrp AS
    (SELECT COUNT(1) cnt,
                   SUM(marks_obtained) mkobt,
                   rollno
            FROM   score
            GROUP BY rollno)
    SELECT cnt, mkobt, rollno, cnt*100 AS cnt_x100
    FROM   scgrp;
     
  5. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    :rolleyes: But I did. Not so much for the OP as it was intended for you. I'm not entirely with "WITH" and all its particular uses, hence the 'With?'.

    CJ
     
  6. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
  7. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    The WITH clause can be used in most places in lieu of a subquery. It doesn't tend to be my initial solution unless there is a reason for it -- mainly because I have been using subqueries for a lot more years than WITH has been an option. There are cases where WITH can provide a performance advantage, but this isn't one of them.
     
    mukulverma2408 and DTSIGuy like this.