CODEWITHSUNDEEP
sqloracle-databasecase
Lyndey
Lyndey

Reputation: 77

Get all results from Case statement on to one row

I'm trying to get results from multiple months into one row:

SELECT id, 
       NAME, 
       year, 
       period, 
       CASE 
         WHEN sub_period = 'S1' THEN Sum(credits) 
         ELSE 0 
       END Tot_S1_Cred, 
       CASE 
         WHEN sub_period = 'S2' THEN Sum(credits) 
         ELSE 0 
       END Tot_S2_Cred, 
       CASE 
         WHEN sub_period NOT IN ( 'S1', 'S2' ) THEN Sum(credits) 
         ELSE 0 
       END Tot_Other_Cred 
FROM   student 
WHERE  period = '201750' 
       AND status IN ( 'RE', 'RW' ) 
GROUP  BY id, 
          NAME, 
          year, 
          period, 
          sub_period

Currently, I'm getting a row for each valid case statement:

|ID    | NAME    |YEAR   |PERIOD   |S1_CRED   |S2     |Other
|---   |----     |-------|------   |-------   |------ |------
|1     |Abbott   |2017   |201750   |0         |3      | 0
|1     |Abbott   |2017   |201750   |0         |0      |0
|2     |Costello |2017   |201750   |0         |3      |0
|3     |Popeye   |2017   |201750   |3         |0      |0
|3     |Popeye   |2017   |201750   |0         |6      |0
|3     |Popeye   |2017   |201750   |0         |0      |9

Upvotes: 0

Views: 1686

Answers (3)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

I think you want a conditional SUM, so put the CASE inside the SUM()

SELECT id, 
       NAME, 
       year, 
       period, 
       SUM ( CASE WHEN sub_period = 'S1' 
                  THEN credits
                  ELSE 0 
             END
           ) as Tot_S1_Cred, 
       SUM ( CASE WHEN sub_period = 'S2' 
                  THEN credits
                  ELSE 0 
             END
           ) as Tot_S2_Cred, 
       SUM ( CASE WHEN sub_period NOT IN ('S1', 'S2')
                  THEN credits
                  ELSE 0 
             END
           ) as Tot_Other_Cred,   
FROM   student 
WHERE  period = '201750' 
       AND status IN ( 'RE', 'RW' ) 
GROUP  BY id, 
          NAME, 
          year, 
          period

Upvotes: 2

PeterH
PeterH

Reputation: 1040

replace your case statments with :

   Case When sub_period = 'S1'                       Then 'Tot_S1_Cred'
        When sub_period = 'S2'                       Then 'Tot_S2_Cred'
        When sub_period NOT IN ( 'S1', 'S2' )        Then 'Tot_Other_Cred'
        Else 'NA' End   As 'Credit Type'

   ,Sum(credits) As 'Amount'

Full Query:

SELECT id, 
       NAME, 
       year, 
       period, 
       Case When sub_period = 'S1'                  Then 'Tot_S1_Cred'
            When sub_period = 'S2'                  Then 'Tot_S2_Cred'
            When sub_period NOT IN ( 'S1', 'S2' )   Then 'Tot_Other_Cred'
            Else 'NA' End   As 'Credit Type'
        ,SUM(credits)

FROM   student 
WHERE  period = '201750' 
       AND status IN ( 'RE', 'RW' ) 
GROUP  BY id, 
          NAME, 
          year, 
          period,

Upvotes: 0

Damon Dudek
Damon Dudek

Reputation: 181

You need to user the same case:

select Id
      ,name
      ,year
      ,Period
      ,case
         when Sub_Period in ('S1', 'S2')  then
          sum(Credits)
         else
          sum(Credits)
       end Tot_S1_Cred
  from Student
 where Period = '201750'
   and Status in ('RE', 'RW')
 group by Id
         ,name
         ,year
         ,Period
         ,Sub_Period

Upvotes: 0

Related Questions