CODEWITHSUNDEEP
oracle-databaseviewsum
jaykio77
jaykio77

Reputation: 391

Using sum in Oracle SQL view to add column total in each row

i have a simple Oracle View with 3 columns team, minors, adults as follows:-

 create view main_summary ( team, minors, adults)
  as 
     select all_teams.team_name as team , 
     case when age<18 then count(id) as minors,
     case when age>= 18 then count(id) as adults
     from all_teams ;

a select statement return rows like:-

    -----------------------------
       team   |   minors  | adults
     -----------------------------
      volleyball    2        4
      football      6        3
       tennis       4        8
    -------------------------------

i want to add a total column in the end which should make the view look like:-

    --------------------------------------
       team      | minors | adults| total
    --------------------------------------
      volleyball    2        4       6
      football      6        3       9
      tennis        4        8       12  
    -----------------------------------------

i tried following, but nothing worked:-

    create view main_summary( team, minors, adults, sum(minors+adults)) ...
     and
     create view main_summary ( team, minors, adults, total)
  as 
     select all_teams.team_name as team , 
     case when age<18 then count(id) as minors,
     case when age>= 18 then count(id) as adults
     sum (  case when age<18 then count(id) + 
       case when age>= 18 then count(id) ) as total ...

The syntax may not be correct as i am not copying directly from my Database. However, pseudo code remains the same. Please guide me how to achieve this.

Upvotes: 0

Views: 2723

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I have no idea why your original view works. I think you want:

create view main_summary as 
     select t.team_name as team , 
            sum(case when age < 18 then 1 else 0 end) as minors,
            sum(case when age >= 18 then 1 else 0 end) as adults,
            count(*) as total
     from all_teams
     group by t.team_name;

Upvotes: 2

Related Questions