1

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

  1. Define short, medium-lengthed, and long campaigns
  2. See how much each length of campaign raise
  3. 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

3
  • 2
    can you please add also the data inclusive table definitions that you got regarding your results, a sanitized version is enough and the expected result would also help
    – nbk
    Jul 20, 2020 at 9:47
  • 1
    Imagine that a campaign have 2 rows with different currencies. What SUM() do you want to be returned by the CASE? You need SUM(CASE currency.name ...).
    – Akina
    Jul 20, 2020 at 16:27
  • Start by making a separate table with just the currency_code and conversion rate. That will be easier to update. Then use a JOIN and toss the CASE.
    – Rick James
    Jul 20, 2020 at 22:28

2 Answers 2

0

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

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.