CODEWITHSUNDEEP
sqloracle-database
Americo
Americo

Reputation: 919

SQL Oracle Combining Multiple Results Rows

I have the below query

Select 
case upper(device_model)
        when 'IPHONE' then 'iOS - iPhone'
        when 'IPAD' then 'iOS - iPad'
        when 'IPOD TOUCH' then 'iOS - iPod Touch'
        Else 'Android'
        End As Device_Model,
count(create_dtime) as Installs_Oct17_Oct30
From Player
Where Create_Dtime >= To_Date('2012-Oct-17','yyyy-mon-dd')
And Create_Dtime <= To_Date('2012-Oct-30','yyyy-mon-dd')
Group By Device_Model
Order By Device_Model

This spits out multiple rows of results that read "Android"....I would like there to be only 4 results rows, one for each case....so it comes out like this:

Device_Model     Installs_Oct17_Oct30
Android            987
iOS - iPad         12003
iOS - iPhone       8563
iOS- iPod Touch    3482

Upvotes: 0

Views: 140

Answers (2)

logan
logan

Reputation: 8366

no need to GroupBy by big field... Just use below simple query.. [your sql] Group By 1 Order By Device_Model

it means you are grouping by field 1 in the select query.. Or try giving alias name.. Group by aliasname order by device_model

Upvotes: 0

Nick.Mc
Nick.Mc

Reputation: 19235

You are grouping by the field device_model, not your expression:

Select 
case upper(device_model)
    when 'IPHONE' then 'iOS - iPhone'
    when 'IPAD' then 'iOS - iPad'
    when 'IPOD TOUCH' then 'iOS - iPod Touch'
    Else 'Android'
    End As Device_Model,
count(create_dtime) as Installs_Oct17_Oct30
From Player
Where Create_Dtime >= To_Date('2012-Oct-17','yyyy-mon-dd')
And Create_Dtime <= To_Date('2012-Oct-30','yyyy-mon-dd')
Group By
case upper(device_model)
    when 'IPHONE' then 'iOS - iPhone'
    when 'IPAD' then 'iOS - iPad'
    when 'IPOD TOUCH' then 'iOS - iPod Touch'
    Else 'Android'
    End
Order By Device_Model

Upvotes: 4

Related Questions