MySQL - GROUP BY not working with multiple CASE WHEN statements

Question

I am working on a database with Kickstarter database, and I’m trying to:

Define short, medium-lengthed, and long campaigns
See how much each length of campaign raise
Convert different currencies into USD

My original code is as follows:

SELECT 
CASE 
    WHEN DATEDIFF(campaign.deadline,campaign.launched)<=30 THEN 'Short Campaign' 
    WHEN DATEDIFF(campaign.deadline,campaign.launched)>30 AND DATEDIFF(campaign.deadline,campaign.launched)<=60 THEN 'Mid-lengthed Campaign'
    ELSE 'Long Campaign'
    END AS 'Campaign Length',
CASE 
    WHEN currency.name='GBP' THEN ROUND(SUM(campaign.pledged)*0.80,2)
    WHEN currency.name='CAD' THEN ROUND(SUM(campaign.pledged)*1.36)
    WHEN currency.name='AUD' THEN ROUND(SUM(campaign.pledged)*1.43)
    WHEN currency.name='NOK' THEN ROUND(SUM(campaign.pledged)*9.28)
    WHEN currency.name='EUR' THEN ROUND(SUM(campaign.pledged)*0.87)
    WHEN currency.name='MXN' THEN ROUND(SUM(campaign.pledged)*22.48)
    WHEN currency.name='SEK' THEN ROUND(SUM(campaign.pledged)*0.04)
    WHEN currency.name='NZD' THEN ROUND(SUM(campaign.pledged)*1.53)
    WHEN currency.name='CHF' THEN ROUND(SUM(campaign.pledged)*0.94)
    WHEN currency.name='DKK' THEN ROUND(SUM(campaign.pledged)*6.52)
    WHEN currency.name='HKD' THEN ROUND(SUM(campaign.pledged)*7.75)
    WHEN currency.name='SGD' THEN ROUND(SUM(campaign.pledged)*1.39)
    WHEN currency.name='JPY' THEN ROUND(SUM(campaign.pledged)*107.11)
    ELSE ROUND(SUM(campaign.pledged),2)
END AS 'Amount Raised'
FROM campaign
LEFT JOIN currency ON currency.id=campaign.currency_id
GROUP BY `Campaign Length`;

I’m expecting a result of:

Campaign Length.      ---- Amount Raised
Short Campaign.       ---- XXXXXXXXXXXXX
Mid-Lengthed Campaign ---- XXXXXXXXXXXXX
Long Campaign.        ---- XXXXXXXXXXXXX

Where XXXXXX is the aggregate of the pledged amount after conversion. The original code shows:

Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ks_data.currency.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

So I improvised by adding additional GROUP BY, after realizing I can’t GROUP BY Amount Raised:

SELECT 
CASE 
    WHEN DATEDIFF(campaign.deadline,campaign.launched)<=30 THEN 'Short Campaign' 
    WHEN DATEDIFF(campaign.deadline,campaign.launched)>30 AND DATEDIFF(campaign.deadline,campaign.launched)<=60 THEN 'Mid-lengthed Campaign'
    ELSE 'Long Campaign'
    END AS 'Campaign Length',
CASE 
    WHEN currency.name='GBP' THEN ROUND(SUM(campaign.pledged)*0.80,2)
    WHEN currency.name='CAD' THEN ROUND(SUM(campaign.pledged)*1.36)
    WHEN currency.name='AUD' THEN ROUND(SUM(campaign.pledged)*1.43)
    WHEN currency.name='NOK' THEN ROUND(SUM(campaign.pledged)*9.28)
    WHEN currency.name='EUR' THEN ROUND(SUM(campaign.pledged)*0.87)
    WHEN currency.name='MXN' THEN ROUND(SUM(campaign.pledged)*22.48)
    WHEN currency.name='SEK' THEN ROUND(SUM(campaign.pledged)*0.04)
    WHEN currency.name='NZD' THEN ROUND(SUM(campaign.pledged)*1.53)
    WHEN currency.name='CHF' THEN ROUND(SUM(campaign.pledged)*0.94)
    WHEN currency.name='DKK' THEN ROUND(SUM(campaign.pledged)*6.52)
    WHEN currency.name='HKD' THEN ROUND(SUM(campaign.pledged)*7.75)
    WHEN currency.name='SGD' THEN ROUND(SUM(campaign.pledged)*1.39)
    WHEN currency.name='JPY' THEN ROUND(SUM(campaign.pledged)*107.11)
    ELSE ROUND(SUM(campaign.pledged),2)
END AS 'Amount Raised'
FROM campaign
LEFT JOIN currency ON currency.id=campaign.currency_id
GROUP BY `Campaign Length`,currency.id;

But the result is instead not grouping as I intended:

Campaign Length.      ---- Amount Raised
Short Campaign.       ---- XXXXXXXXXXXXX
Mid-Lengthed Campaign ---- XXXXXXXXXXXXX
Long Campaign.        ---- XXXXXXXXXXXXX
Mid-Lengthed Campaign ---- XXXXXXXXXXXXX
Mid-Lengthed Campaign ---- XXXXXXXXXXXXX
Short Campaign.       ---- XXXXXXXXXXXXX

I’ve tried many ways, but couldn’t find a solution

mysql subquery

bbaird · Answer

You need to do the currency conversion within the aggregate function

SELECT 
  CASE 
    WHEN DATEDIFF(campaign.deadline,campaign.launched)<=30 THEN 'Short Campaign' 
    WHEN DATEDIFF(campaign.deadline,campaign.launched)>30 AND DATEDIFF(campaign.deadline,campaign.launched)<=60 THEN 'Mid-lengthed Campaign'
    ELSE 'Long Campaign'
  END AS 'Campaign Length'
 ,ROUND
    (  
      SUM
        (
          CASE 
            WHEN currency.name='GBP' THEN campaign.pledged*0.80
            WHEN currency.name='CAD' THEN campaign.pledged*1.36
            WHEN currency.name='AUD' THEN campaign.pledged*1.43
            WHEN currency.name='NOK' THEN campaign.pledged*9.28
            WHEN currency.name='EUR' THEN campaign.pledged*0.87
            WHEN currency.name='MXN' THEN campaign.pledged*22.48
            WHEN currency.name='SEK' THEN campaign.pledged*0.04
            WHEN currency.name='NZD' THEN campaign.pledged*1.53
            WHEN currency.name='CHF' THEN campaign.pledged*0.94
            WHEN currency.name='DKK' THEN campaign.pledged*6.52
            WHEN currency.name='HKD' THEN campaign.pledged*7.75
            WHEN currency.name='SGD' THEN campaign.pledged*1.39
            WHEN currency.name='JPY' THEN campaign.pledged*107.11
            ELSE ROUND(SUM(campaign.pledged),2)
          END
        )
     ,2
    ) AS 'Amount Raised'
FROM 
  campaign
LEFT JOIN 
  currency 
    ON currency.id=campaign.currency_id
GROUP BY
  CASE 
    WHEN DATEDIFF(campaign.deadline,campaign.launched)<=30 THEN 'Short Campaign' 
    WHEN DATEDIFF(campaign.deadline,campaign.launched)>30 AND DATEDIFF(campaign.deadline,campaign.launched)<=60 THEN 'Mid-lengthed Campaign'
    ELSE 'Long Campaign'
  END

Answered by bbaird on October 28, 2021

Lennart · Answer

A syntactical version that should work (untested due to lack of ddl and sample data) is:

SELECT 
CASE 
    WHEN DATEDIFF(campaign.deadline,campaign.launched)<=30 THEN 'Short Campaign' 
    WHEN DATEDIFF(campaign.deadline,campaign.launched)>30 AND DATEDIFF(campaign.deadline,campaign.launched)<=60 THEN 'Mid-lengthed Campaign'
    ELSE 'Long Campaign'
    END AS 'Campaign Length',
CASE 
    WHEN currency.name='GBP' THEN ROUND(SUM(campaign.pledged)*0.80,2)
    WHEN currency.name='CAD' THEN ROUND(SUM(campaign.pledged)*1.36)
    WHEN currency.name='AUD' THEN ROUND(SUM(campaign.pledged)*1.43)
    WHEN currency.name='NOK' THEN ROUND(SUM(campaign.pledged)*9.28)
    WHEN currency.name='EUR' THEN ROUND(SUM(campaign.pledged)*0.87)
    WHEN currency.name='MXN' THEN ROUND(SUM(campaign.pledged)*22.48)
    WHEN currency.name='SEK' THEN ROUND(SUM(campaign.pledged)*0.04)
    WHEN currency.name='NZD' THEN ROUND(SUM(campaign.pledged)*1.53)
    WHEN currency.name='CHF' THEN ROUND(SUM(campaign.pledged)*0.94)
    WHEN currency.name='DKK' THEN ROUND(SUM(campaign.pledged)*6.52)
    WHEN currency.name='HKD' THEN ROUND(SUM(campaign.pledged)*7.75)
    WHEN currency.name='SGD' THEN ROUND(SUM(campaign.pledged)*1.39)
    WHEN currency.name='JPY' THEN ROUND(SUM(campaign.pledged)*107.11)
    ELSE ROUND(SUM(campaign.pledged),2)
END AS 'Amount Raised'
FROM campaign
LEFT JOIN currency ON currency.id=campaign.currency_id
GROUP BY CASE 
    WHEN DATEDIFF(campaign.deadline,campaign.launched)<=30 THEN 'Short Campaign' 
    WHEN DATEDIFF(campaign.deadline,campaign.launched)>30 AND DATEDIFF(campaign.deadline,campaign.launched)<=60 THEN 'Mid-lengthed Campaign'
    ELSE 'Long Campaign'
    END, currency.name;

I agree with @Akina that the result will be confusing when several currencies are involved. The result may end up like:

Long Campaign   42
Long Campaign   36
...

and you basically have no idea what row that represents which currency

MySQL - GROUP BY not working with multiple CASE WHEN statements

2 Answers

Add your own answers!

Ask a Question