CODEWITHSUNDEEP
mysqlsql
David542
David542

Reputation: 110237

Subquery for comparison

I have the following data in my aggregation query:

name        release_year    iTunes                    Google                Microsoft
10 to Mid   2003            SDBUY,HDRENT,SDRENT       NULL                  NULL
100 Girls   2001            HDBUY                     HDBUY,HDRENT,SDRENT   SDBUY,SDRENT
100 Rifles  NULL            NULL                      HDBUY                 HDBUY,HDRENT

From this I would like to build the following result:

name         release_year    offers_on_itunes      top_platform    top_platform_offers
10 to Mid    2003            SDBUY,HDRENT,SDRENT   iTunes          SDBUY,HDRENT,SDREN
100 Girls    2001            HDBUY                 Google          SDBUY,SDRENT
100 Rifles   NULL            NULL                  Microsoft       HDBUY,HDRENT

In other words, we can get the first three columns in the result by doing:

SELECT name, release_year, itunes AS offers_on_itunes FROM (subquery)

But what would be the best way to get the top platform and the top platform offers? Perhaps doing CASE based on LEN() would get the top_platform but I'm not quite sure about the other. How would this best be constructed?

Upvotes: 1

Views: 70

Answers (1)

Sᴀᴍ Onᴇᴌᴀ
Sᴀᴍ Onᴇᴌᴀ

Reputation: 8297

You could use an if function, like below. I know it might get even messier comparing three columns, but hopefully this puts you on the right track...

SELECT name, release_year, itunes as offers_on_itunes,Google,
IF (itunes is null,0,
    ROUND (   
    (
        LENGTH(itunes)
        - LENGTH( REPLACE ( itunes, ",", "") ) 
    ) / LENGTH(",")  
    )  +1
) as numItunesOfferings,

IF (Google is null,0,
ROUND (   
    (
        LENGTH(Google)
        - LENGTH( REPLACE ( Google, ",", "") ) 
    ) / LENGTH(",")  
)  +1
) as numGoogleOfferings,
if (
    IF (itunes is null,0,
    ROUND (   
    (
        LENGTH(itunes)
        - LENGTH( REPLACE ( itunes, ",", "") ) 
    ) / LENGTH(",")  
    )  +1
) > IF (Google is null,0,
ROUND (   
    (
        LENGTH(Google)
        - LENGTH( REPLACE ( Google, ",", "") ) 
    ) / LENGTH(",")  
    )  +1
),'itunes','Google') as top_platform
FROM (subquery)

Upvotes: 1

Related Questions