Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 335
  • Last Modified:

Mysql Case statement

Hi Experts

I want to use a case statement within a select statement to return a value.
I am not sure on how to go about doing this process correctly.

The idea is as I have theme_keywords.CompetingPages value and I want to run through the Case to find the match and then do a calculation to get a value for the row. I then want to assign this value to a column called 'BL' but am not sure how to do this.

The end result will be the data from the select statement + the 'BL column with the correct value in each row.

Hope this makes sense.
I am using the  @TK_CompetingPages as the parameter to hold the value.

I would appreciate it if someone could show me the correct way of doing this as I have been battling with this for a week.


SELECT
theme_keywords.CompetingPages,

DECLARE @TK_CompetingPages INTEGER AS `BL`;
SET @TK_CompetingPages = theme_keywords.CompetingPages;

CASE
WHEN @TK_CompetingPages   < 50000  THEN   @TK_CompetingPages/10000		
WHEN @TK_CompetingPages   > 50001 AND  @TK_CompetingPages   <= 100000 THEN  @TK_CompetingPages /10000		
WHEN @TK_CompetingPages   > 100001 AND  @TK_CompetingPages   <= 300000 THEN  @TK_CompetingPages /10000		
WHEN @TK_CompetingPages   > 300001 AND  @TK_CompetingPages   <= 500000 THEN  @TK_CompetingPages /10000		
WHEN @TK_CompetingPages   > 500001 AND  @TK_CompetingPages   <= 700000 THEN  @TK_CompetingPages /10000		
WHEN @TK_CompetingPages   > 700001 AND  @TK_CompetingPages   <= 900000 THEN  @TK_CompetingPages /10000		
WHEN @TK_CompetingPages   > 900001 AND  @TK_CompetingPages   <= 1100000 THEN  @TK_CompetingPages /10000		
WHEN @TK_CompetingPages   > 1100001 AND  @TK_CompetingPages   <= 2000000 THEN  @TK_CompetingPages /8000		
WHEN @TK_CompetingPages   > 2000001 AND  @TK_CompetingPages   <= 4000000 THEN  @TK_CompetingPages /8500		
WHEN @TK_CompetingPages   > 4000001 AND  @TK_CompetingPages   <= 6000000 THEN  @TK_CompetingPages /9000		
WHEN @TK_CompetingPages   > 6000001 AND  @TK_CompetingPages   <= 8000000 THEN  @TK_CompetingPages /9500		
WHEN @TK_CompetingPages   > 8000001 AND  @TK_CompetingPages   <= 10000000 THEN  @TK_CompetingPages /10000		
WHEN @TK_CompetingPages   > 10000001 AND  @TK_CompetingPages   <= 12000000 THEN  @TK_CompetingPages /10500		
WHEN @TK_CompetingPages   > 12000001 AND  @TK_CompetingPages   <= 14000000 THEN  @TK_CompetingPages /11000		
WHEN @TK_CompetingPages   > 14000001 AND  @TK_CompetingPages   <= 16000000 THEN  @TK_CompetingPages /11500		
WHEN @TK_CompetingPages   > 16000001 AND  @TK_CompetingPages   <= 18000000 THEN  @TK_CompetingPages /12000		
WHEN @TK_CompetingPages   > 18000001 AND  @TK_CompetingPages   <= 20000000 THEN  @TK_CompetingPages /12500		
WHEN @TK_CompetingPages   > 20000001 AND  @TK_CompetingPages   <= 22000000 THEN  @TK_CompetingPages /13000		
WHEN @TK_CompetingPages   > 22000001 AND  @TK_CompetingPages   <= 24000000 THEN  @TK_CompetingPages /13500		
WHEN @TK_CompetingPages   > 24000001 AND  @TK_CompetingPages   <= 26000000 THEN  @TK_CompetingPages /14000		
WHEN @TK_CompetingPages   > 26000001 AND  @TK_CompetingPages   <= 28000000 THEN  @TK_CompetingPages /14500		
WHEN @TK_CompetingPages   > 28000001 AND  @TK_CompetingPages   <= 30000000 THEN  @TK_CompetingPages /15000		
WHEN @TK_CompetingPages   > 30000001 AND  @TK_CompetingPages   <= 32000000 THEN  @TK_CompetingPages /15500		
WHEN @TK_CompetingPages   > 32000001 AND  @TK_CompetingPages   <= 34000000 THEN  @TK_CompetingPages /16000		
WHEN @TK_CompetingPages   > 34000001 AND  @TK_CompetingPages   <= 36000000 THEN  @TK_CompetingPages /16500		
WHEN @TK_CompetingPages   > 36000001 AND  @TK_CompetingPages   <= 38000000 THEN  @TK_CompetingPages /17000		
WHEN @TK_CompetingPages   > 38000001 AND  @TK_CompetingPages   <= 40000000 THEN  @TK_CompetingPages /17500		
WHEN @TK_CompetingPages   > 40000001 AND  @TK_CompetingPages   <= 42000000 THEN  @TK_CompetingPages /18000		
WHEN @TK_CompetingPages   > 42000001 AND  @TK_CompetingPages   <= 44000000 THEN  @TK_CompetingPages /18500		
WHEN @TK_CompetingPages   > 44000001 AND  @TK_CompetingPages   <= 46000000 THEN  @TK_CompetingPages /19000		
WHEN @TK_CompetingPages   > 46000001 AND  @TK_CompetingPages   <= 48000000 THEN  @TK_CompetingPages /19500		
WHEN @TK_CompetingPages   > 48000001 AND  @TK_CompetingPages   <= 50000000 THEN  @TK_CompetingPages /20000		
WHEN @TK_CompetingPages   > 50000001 AND  @TK_CompetingPages   <= 52000000 THEN  @TK_CompetingPages /20500		
WHEN @TK_CompetingPages   > 52000001 AND  @TK_CompetingPages   <= 54000000 THEN  @TK_CompetingPages /21000		
WHEN @TK_CompetingPages   > 54000001 AND  @TK_CompetingPages   <= 56000000 THEN  @TK_CompetingPages /21500		
WHEN @TK_CompetingPages   > 56000001 AND  @TK_CompetingPages   <= 58000000 THEN  @TK_CompetingPages /22000		
WHEN @TK_CompetingPages   > 58000001 AND  @TK_CompetingPages   <= 60000000 THEN  @TK_CompetingPages /22500		
WHEN @TK_CompetingPages   > 60000001 AND  @TK_CompetingPages   <= 62000000 THEN  @TK_CompetingPages /23000		
WHEN @TK_CompetingPages   > 62000001 AND  @TK_CompetingPages   <= 64000000 THEN  @TK_CompetingPages /23500		
WHEN @TK_CompetingPages   > 64000001 AND  @TK_CompetingPages   <= 66000000 THEN  @TK_CompetingPages /24000		
WHEN @TK_CompetingPages   > 66000001 AND  @TK_CompetingPages   <= 68000000 THEN  @TK_CompetingPages /24500		
WHEN @TK_CompetingPages   > 68000001 AND  @TK_CompetingPages   <= 70000000 THEN  @TK_CompetingPages /25000		
WHEN @TK_CompetingPages   > 70000001 AND  @TK_CompetingPages   <= 72000000 THEN  @TK_CompetingPages /25500		
WHEN @TK_CompetingPages   > 72000001 AND  @TK_CompetingPages   <= 74000000 THEN  @TK_CompetingPages /26000		
WHEN @TK_CompetingPages   > 74000001 AND  @TK_CompetingPages   <= 76000000 THEN  @TK_CompetingPages /26500		
WHEN @TK_CompetingPages   > 76000001 AND  @TK_CompetingPages   <= 78000000 THEN  @TK_CompetingPages /27000		
WHEN @TK_CompetingPages   > 78000001 AND  @TK_CompetingPages   <= 80000000 THEN  @TK_CompetingPages /27500		
WHEN @TK_CompetingPages   > 80000001 AND  @TK_CompetingPages   <= 82000000 THEN  @TK_CompetingPages /28000		
WHEN @TK_CompetingPages   > 82000001 AND  @TK_CompetingPages   <= 84000000 THEN  @TK_CompetingPages /28500		
WHEN @TK_CompetingPages   > 84000001 AND  @TK_CompetingPages   <= 86000000 THEN  @TK_CompetingPages /29000		
WHEN @TK_CompetingPages   > 86000001 AND  @TK_CompetingPages   <= 88000000 THEN  @TK_CompetingPages /29500		
WHEN @TK_CompetingPages   > 88000001 AND  @TK_CompetingPages   <= 90000000 THEN  @TK_CompetingPages /30000		
WHEN @TK_CompetingPages   > 90000001 AND  @TK_CompetingPages   <= 92000000 THEN  @TK_CompetingPages /30500		
WHEN @TK_CompetingPages   > 92000001 AND  @TK_CompetingPages   <= 94000000 THEN  @TK_CompetingPages /31000		
WHEN @TK_CompetingPages   > 94000001 AND  @TK_CompetingPages   <= 96000000 THEN  @TK_CompetingPages /31500		
WHEN @TK_CompetingPages   > 96000001 AND  @TK_CompetingPages   <= 98000000 THEN  @TK_CompetingPages /32000		
WHEN @TK_CompetingPages   > 98000001 AND  @TK_CompetingPages   <= 100000000 THEN  @TK_CompetingPages /32500		
WHEN @TK_CompetingPages   > 100000001 AND  @TK_CompetingPages   <= 102000000 THEN  @TK_CompetingPages /33000		
WHEN @TK_CompetingPages   > 102000001 AND  @TK_CompetingPages   <= 104000000 THEN  @TK_CompetingPages /33500		
WHEN @TK_CompetingPages   > 104000001 AND  @TK_CompetingPages   <= 106000000 THEN  @TK_CompetingPages /34000		
WHEN @TK_CompetingPages   > 106000001 AND  @TK_CompetingPages   <= 108000000 THEN  @TK_CompetingPages /34500		
WHEN @TK_CompetingPages   > 108000001 AND  @TK_CompetingPages   <= 110000000 THEN  @TK_CompetingPages /35000		
WHEN @TK_CompetingPages   > 110000001 AND  @TK_CompetingPages   <= 112000000 THEN  @TK_CompetingPages /35500		
WHEN @TK_CompetingPages   > 112000001 AND  @TK_CompetingPages   <= 114000000 THEN  @TK_CompetingPages /36000		
WHEN @TK_CompetingPages   > 114000001 AND  @TK_CompetingPages   <= 116000000 THEN  @TK_CompetingPages /36500		
WHEN @TK_CompetingPages   > 116000001 AND  @TK_CompetingPages   <= 118000000 THEN  @TK_CompetingPages /37000		
WHEN @TK_CompetingPages   > 118000001 AND  @TK_CompetingPages   <= 120000000 THEN  @TK_CompetingPages /37500		
WHEN @TK_CompetingPages   > 120000001 AND  @TK_CompetingPages   <= 122000000 THEN  @TK_CompetingPages /38000		
WHEN @TK_CompetingPages   > 122000001 AND  @TK_CompetingPages   <= 124000000 THEN  @TK_CompetingPages /38500		
WHEN @TK_CompetingPages   > 124000001 AND  @TK_CompetingPages   <= 126000000 THEN  @TK_CompetingPages /39000		
WHEN @TK_CompetingPages   > 126000001 AND  @TK_CompetingPages   <= 128000000 THEN  @TK_CompetingPages /39500		
WHEN @TK_CompetingPages   > 128000001 AND  @TK_CompetingPages   <= 130000000 THEN  @TK_CompetingPages /40000		
WHEN @TK_CompetingPages   > 130000001 AND  @TK_CompetingPages   <= 132000000 THEN  @TK_CompetingPages /40500		
WHEN @TK_CompetingPages   > 132000001 AND  @TK_CompetingPages   <= 134000000 THEN  @TK_CompetingPages /41000		
WHEN @TK_CompetingPages   > 134000001 AND  @TK_CompetingPages   <= 136000000 THEN  @TK_CompetingPages /41500		
WHEN @TK_CompetingPages   > 136000001 AND  @TK_CompetingPages   <= 138000000 THEN  @TK_CompetingPages /42000		
WHEN @TK_CompetingPages   > 138000001 AND  @TK_CompetingPages   <= 140000000 THEN  @TK_CompetingPages /42500		
WHEN @TK_CompetingPages   > 140000001 AND  @TK_CompetingPages   <= 142000000 THEN  @TK_CompetingPages /43000		
WHEN @TK_CompetingPages   > 142000001 AND  @TK_CompetingPages   <= 144000000 THEN  @TK_CompetingPages /43500		
WHEN @TK_CompetingPages   > 144000001 AND  @TK_CompetingPages   <= 146000000 THEN  @TK_CompetingPages /44000		
WHEN @TK_CompetingPages   > 146000001 AND  @TK_CompetingPages   <= 148000000 THEN  @TK_CompetingPages /44500		
WHEN @TK_CompetingPages   > 148000001 AND  @TK_CompetingPages   <= 150000000 THEN  @TK_CompetingPages /45000		
WHEN @TK_CompetingPages   > 150000001 AND  @TK_CompetingPages   <= 152000000 THEN  @TK_CompetingPages /45500		
WHEN @TK_CompetingPages   > 152000001 AND  @TK_CompetingPages   <= 154000000 THEN  @TK_CompetingPages /46000		
WHEN @TK_CompetingPages   > 154000001 AND  @TK_CompetingPages   <= 156000000 THEN  @TK_CompetingPages /46500		
WHEN @TK_CompetingPages   > 156000001 AND  @TK_CompetingPages   <= 158000000 THEN  @TK_CompetingPages /47000		
WHEN @TK_CompetingPages   > 158000001 AND  @TK_CompetingPages   <= 160000000 THEN  @TK_CompetingPages /47500		
WHEN @TK_CompetingPages   > 160000001 AND  @TK_CompetingPages   <= 162000000 THEN  @TK_CompetingPages /48000		
WHEN @TK_CompetingPages   > 162000001 AND  @TK_CompetingPages   <= 164000000 THEN  @TK_CompetingPages /48500		
WHEN @TK_CompetingPages   > 164000001 AND  @TK_CompetingPages   <= 166000000 THEN  @TK_CompetingPages /49000		
WHEN @TK_CompetingPages   > 166000001 AND  @TK_CompetingPages   <= 168000000 THEN  @TK_CompetingPages /49500		
WHEN @TK_CompetingPages   > 168000001 AND  @TK_CompetingPages   <= 170000000 THEN  @TK_CompetingPages /50000		
WHEN @TK_CompetingPages   > 170000001 AND  @TK_CompetingPages   <= 172000000 THEN  @TK_CompetingPages /50500		
WHEN @TK_CompetingPages   > 172000001 AND  @TK_CompetingPages   <= 174000000 THEN  @TK_CompetingPages /51000		
WHEN @TK_CompetingPages   > 174000001 AND  @TK_CompetingPages   <= 176000000 THEN  @TK_CompetingPages /51500		
WHEN @TK_CompetingPages   > 176000001 AND  @TK_CompetingPages   <= 178000000 THEN  @TK_CompetingPages /52000		
WHEN @TK_CompetingPages   > 178000001 AND  @TK_CompetingPages   <= 180000000 THEN  @TK_CompetingPages /52500		
WHEN @TK_CompetingPages   > 180000001 AND  @TK_CompetingPages   <= 182000000 THEN  @TK_CompetingPages /53000		
WHEN @TK_CompetingPages   > 182000001 AND  @TK_CompetingPages   <= 184000000 THEN  @TK_CompetingPages /53500		
WHEN @TK_CompetingPages   > 184000001 AND  @TK_CompetingPages   <= 186000000 THEN  @TK_CompetingPages /54000		
WHEN @TK_CompetingPages   > 186000001 AND  @TK_CompetingPages   <= 188000000 THEN  @TK_CompetingPages /54500		
WHEN @TK_CompetingPages   > 188000001 AND  @TK_CompetingPages   <= 190000000 THEN  @TK_CompetingPages /55000		
WHEN @TK_CompetingPages   > 190000001 AND  @TK_CompetingPages   <= 192000000 THEN  @TK_CompetingPages /55500		
WHEN @TK_CompetingPages   > 192000001 AND  @TK_CompetingPages   <= 194000000 THEN  @TK_CompetingPages /56000		
WHEN @TK_CompetingPages   > 194000001 AND  @TK_CompetingPages   <= 196000000 THEN  @TK_CompetingPages /56500		
WHEN @TK_CompetingPages   > 196000001 AND  @TK_CompetingPages   <= 198000000 THEN  @TK_CompetingPages /57000		
WHEN @TK_CompetingPages   > 198000001 AND  @TK_CompetingPages   <= 200000000 THEN  @TK_CompetingPages /57500		
WHEN @TK_CompetingPages   > 200000001 AND  @TK_CompetingPages   <= 202000000 THEN  @TK_CompetingPages /58000		
WHEN @TK_CompetingPages   > 202000001 AND  @TK_CompetingPages   <= 204000000 THEN  @TK_CompetingPages /58500		
WHEN @TK_CompetingPages   > 204000001 AND  @TK_CompetingPages   <= 206000000 THEN  @TK_CompetingPages /59000		
WHEN @TK_CompetingPages   > 206000001 AND  @TK_CompetingPages   <= 208000000 THEN  @TK_CompetingPages /59500		
WHEN @TK_CompetingPages   > 208000001 AND  @TK_CompetingPages   <= 210000000 THEN  @TK_CompetingPages /60000		
WHEN @TK_CompetingPages   > 210000001 AND  @TK_CompetingPages   <= 212000000 THEN  @TK_CompetingPages /60500		
WHEN @TK_CompetingPages   > 212000001 AND  @TK_CompetingPages   <= 214000000 THEN  @TK_CompetingPages /61000		
WHEN @TK_CompetingPages   > 214000001 AND  @TK_CompetingPages   <= 216000000 THEN  @TK_CompetingPages /61500		
WHEN @TK_CompetingPages   > 216000001 AND  @TK_CompetingPages   <= 218000000 THEN  @TK_CompetingPages /62000		
WHEN @TK_CompetingPages   > 218000001 AND  @TK_CompetingPages   <= 220000000 THEN  @TK_CompetingPages /62500		
WHEN @TK_CompetingPages   > 220000001 AND  @TK_CompetingPages   <= 222000000 THEN  @TK_CompetingPages /63000		
ELSE 0
END

theme_keywords.CMPRankingIndex,
FROM
theme_keywords
WHERE
theme_keywords.profileid = 92

Open in new window

0
matthewdacruz
Asked:
matthewdacruz
  • 3
  • 3
1 Solution
 
tigin44Commented:
try this
0
 
tigin44Commented:

SELECT
theme_keywords.CompetingPages,
CASE
	WHEN CompetingPages   < 50000  THEN   CompetingPages/10000		
	WHEN CompetingPages   > 50001 AND  CompetingPages   <= 100000 THEN  CompetingPages /10000		
	WHEN CompetingPages   > 100001 AND  CompetingPages   <= 300000 THEN  CompetingPages /10000		
	WHEN CompetingPages   > 300001 AND  CompetingPages   <= 500000 THEN  CompetingPages /10000		
	WHEN CompetingPages   > 500001 AND  CompetingPages   <= 700000 THEN  CompetingPages /10000		
	WHEN CompetingPages   > 700001 AND  CompetingPages   <= 900000 THEN  CompetingPages /10000		
	WHEN CompetingPages   > 900001 AND  CompetingPages   <= 1100000 THEN  CompetingPages /10000		
	WHEN CompetingPages   > 1100001 AND  CompetingPages   <= 2000000 THEN  CompetingPages /8000		
	WHEN CompetingPages   > 2000001 AND  CompetingPages   <= 4000000 THEN  CompetingPages /8500		
	WHEN CompetingPages   > 4000001 AND  CompetingPages   <= 6000000 THEN  CompetingPages /9000		
	WHEN CompetingPages   > 6000001 AND  CompetingPages   <= 8000000 THEN  CompetingPages /9500		
	WHEN CompetingPages   > 8000001 AND  CompetingPages   <= 10000000 THEN  CompetingPages /10000		
	WHEN CompetingPages   > 10000001 AND  CompetingPages   <= 12000000 THEN  CompetingPages /10500		
	WHEN CompetingPages   > 12000001 AND  CompetingPages   <= 14000000 THEN  CompetingPages /11000		
	WHEN CompetingPages   > 14000001 AND  CompetingPages   <= 16000000 THEN  CompetingPages /11500		
	WHEN CompetingPages   > 16000001 AND  CompetingPages   <= 18000000 THEN  CompetingPages /12000		
	WHEN CompetingPages   > 18000001 AND  CompetingPages   <= 20000000 THEN  CompetingPages /12500		
	WHEN CompetingPages   > 20000001 AND  CompetingPages   <= 22000000 THEN  CompetingPages /13000		
	WHEN CompetingPages   > 22000001 AND  CompetingPages   <= 24000000 THEN  CompetingPages /13500		
	WHEN CompetingPages   > 24000001 AND  CompetingPages   <= 26000000 THEN  CompetingPages /14000		
	WHEN CompetingPages   > 26000001 AND  CompetingPages   <= 28000000 THEN  CompetingPages /14500		
	WHEN CompetingPages   > 28000001 AND  CompetingPages   <= 30000000 THEN  CompetingPages /15000		
	WHEN CompetingPages   > 30000001 AND  CompetingPages   <= 32000000 THEN  CompetingPages /15500		
	WHEN CompetingPages   > 32000001 AND  CompetingPages   <= 34000000 THEN  CompetingPages /16000		
	WHEN CompetingPages   > 34000001 AND  CompetingPages   <= 36000000 THEN  CompetingPages /16500		
	WHEN CompetingPages   > 36000001 AND  CompetingPages   <= 38000000 THEN  CompetingPages /17000		
	WHEN CompetingPages   > 38000001 AND  CompetingPages   <= 40000000 THEN  CompetingPages /17500		
	WHEN CompetingPages   > 40000001 AND  CompetingPages   <= 42000000 THEN  CompetingPages /18000		
	WHEN CompetingPages   > 42000001 AND  CompetingPages   <= 44000000 THEN  CompetingPages /18500		
	WHEN CompetingPages   > 44000001 AND  CompetingPages   <= 46000000 THEN  CompetingPages /19000		
	WHEN CompetingPages   > 46000001 AND  CompetingPages   <= 48000000 THEN  CompetingPages /19500		
	WHEN CompetingPages   > 48000001 AND  CompetingPages   <= 50000000 THEN  CompetingPages /20000		
	WHEN CompetingPages   > 50000001 AND  CompetingPages   <= 52000000 THEN  CompetingPages /20500		
	WHEN CompetingPages   > 52000001 AND  CompetingPages   <= 54000000 THEN  CompetingPages /21000		
	WHEN CompetingPages   > 54000001 AND  CompetingPages   <= 56000000 THEN  CompetingPages /21500		
	WHEN CompetingPages   > 56000001 AND  CompetingPages   <= 58000000 THEN  CompetingPages /22000		
	WHEN CompetingPages   > 58000001 AND  CompetingPages   <= 60000000 THEN  CompetingPages /22500		
	WHEN CompetingPages   > 60000001 AND  CompetingPages   <= 62000000 THEN  CompetingPages /23000		
	WHEN CompetingPages   > 62000001 AND  CompetingPages   <= 64000000 THEN  CompetingPages /23500		
	WHEN CompetingPages   > 64000001 AND  CompetingPages   <= 66000000 THEN  CompetingPages /24000		
	WHEN CompetingPages   > 66000001 AND  CompetingPages   <= 68000000 THEN  CompetingPages /24500		
	WHEN CompetingPages   > 68000001 AND  CompetingPages   <= 70000000 THEN  CompetingPages /25000		
	WHEN CompetingPages   > 70000001 AND  CompetingPages   <= 72000000 THEN  CompetingPages /25500		
	WHEN CompetingPages   > 72000001 AND  CompetingPages   <= 74000000 THEN  CompetingPages /26000		
	WHEN CompetingPages   > 74000001 AND  CompetingPages   <= 76000000 THEN  CompetingPages /26500		
	WHEN CompetingPages   > 76000001 AND  CompetingPages   <= 78000000 THEN  CompetingPages /27000		
	WHEN CompetingPages   > 78000001 AND  CompetingPages   <= 80000000 THEN  CompetingPages /27500		
	WHEN CompetingPages   > 80000001 AND  CompetingPages   <= 82000000 THEN  CompetingPages /28000		
	WHEN CompetingPages   > 82000001 AND  CompetingPages   <= 84000000 THEN  CompetingPages /28500		
	WHEN CompetingPages   > 84000001 AND  CompetingPages   <= 86000000 THEN  CompetingPages /29000		
	WHEN CompetingPages   > 86000001 AND  CompetingPages   <= 88000000 THEN  CompetingPages /29500		
	WHEN CompetingPages   > 88000001 AND  CompetingPages   <= 90000000 THEN  CompetingPages /30000		
	WHEN CompetingPages   > 90000001 AND  CompetingPages   <= 92000000 THEN  CompetingPages /30500		
	WHEN CompetingPages   > 92000001 AND  CompetingPages   <= 94000000 THEN  CompetingPages /31000		
	WHEN CompetingPages   > 94000001 AND  CompetingPages   <= 96000000 THEN  CompetingPages /31500		
	WHEN CompetingPages   > 96000001 AND  CompetingPages   <= 98000000 THEN  CompetingPages /32000		
	WHEN CompetingPages   > 98000001 AND  CompetingPages   <= 100000000 THEN  CompetingPages /32500		
	WHEN CompetingPages   > 100000001 AND  CompetingPages   <= 102000000 THEN  CompetingPages /33000		
	WHEN CompetingPages   > 102000001 AND  CompetingPages   <= 104000000 THEN  CompetingPages /33500		
	WHEN CompetingPages   > 104000001 AND  CompetingPages   <= 106000000 THEN  CompetingPages /34000		
	WHEN CompetingPages   > 106000001 AND  CompetingPages   <= 108000000 THEN  CompetingPages /34500		
	WHEN CompetingPages   > 108000001 AND  CompetingPages   <= 110000000 THEN  CompetingPages /35000		
	WHEN CompetingPages   > 110000001 AND  CompetingPages   <= 112000000 THEN  CompetingPages /35500		
	WHEN CompetingPages   > 112000001 AND  CompetingPages   <= 114000000 THEN  CompetingPages /36000		
	WHEN CompetingPages   > 114000001 AND  CompetingPages   <= 116000000 THEN  CompetingPages /36500		
	WHEN CompetingPages   > 116000001 AND  CompetingPages   <= 118000000 THEN  CompetingPages /37000		
	WHEN CompetingPages   > 118000001 AND  CompetingPages   <= 120000000 THEN  CompetingPages /37500		
	WHEN CompetingPages   > 120000001 AND  CompetingPages   <= 122000000 THEN  CompetingPages /38000		
	WHEN CompetingPages   > 122000001 AND  CompetingPages   <= 124000000 THEN  CompetingPages /38500		
	WHEN CompetingPages   > 124000001 AND  CompetingPages   <= 126000000 THEN  CompetingPages /39000		
	WHEN CompetingPages   > 126000001 AND  CompetingPages   <= 128000000 THEN  CompetingPages /39500		
	WHEN CompetingPages   > 128000001 AND  CompetingPages   <= 130000000 THEN  CompetingPages /40000		
	WHEN CompetingPages   > 130000001 AND  CompetingPages   <= 132000000 THEN  CompetingPages /40500		
	WHEN CompetingPages   > 132000001 AND  CompetingPages   <= 134000000 THEN  CompetingPages /41000		
	WHEN CompetingPages   > 134000001 AND  CompetingPages   <= 136000000 THEN  CompetingPages /41500		
	WHEN CompetingPages   > 136000001 AND  CompetingPages   <= 138000000 THEN  CompetingPages /42000		
	WHEN CompetingPages   > 138000001 AND  CompetingPages   <= 140000000 THEN  CompetingPages /42500		
	WHEN CompetingPages   > 140000001 AND  CompetingPages   <= 142000000 THEN  CompetingPages /43000		
	WHEN CompetingPages   > 142000001 AND  CompetingPages   <= 144000000 THEN  CompetingPages /43500		
	WHEN CompetingPages   > 144000001 AND  CompetingPages   <= 146000000 THEN  CompetingPages /44000		
	WHEN CompetingPages   > 146000001 AND  CompetingPages   <= 148000000 THEN  CompetingPages /44500		
	WHEN CompetingPages   > 148000001 AND  CompetingPages   <= 150000000 THEN  CompetingPages /45000		
	WHEN CompetingPages   > 150000001 AND  CompetingPages   <= 152000000 THEN  CompetingPages /45500		
	WHEN CompetingPages   > 152000001 AND  CompetingPages   <= 154000000 THEN  CompetingPages /46000		
	WHEN CompetingPages   > 154000001 AND  CompetingPages   <= 156000000 THEN  CompetingPages /46500		
	WHEN CompetingPages   > 156000001 AND  CompetingPages   <= 158000000 THEN  CompetingPages /47000		
	WHEN CompetingPages   > 158000001 AND  CompetingPages   <= 160000000 THEN  CompetingPages /47500		
	WHEN CompetingPages   > 160000001 AND  CompetingPages   <= 162000000 THEN  CompetingPages /48000		
	WHEN CompetingPages   > 162000001 AND  CompetingPages   <= 164000000 THEN  CompetingPages /48500		
	WHEN CompetingPages   > 164000001 AND  CompetingPages   <= 166000000 THEN  CompetingPages /49000		
	WHEN CompetingPages   > 166000001 AND  CompetingPages   <= 168000000 THEN  CompetingPages /49500		
	WHEN CompetingPages   > 168000001 AND  CompetingPages   <= 170000000 THEN  CompetingPages /50000		
	WHEN CompetingPages   > 170000001 AND  CompetingPages   <= 172000000 THEN  CompetingPages /50500		
	WHEN CompetingPages   > 172000001 AND  CompetingPages   <= 174000000 THEN  CompetingPages /51000		
	WHEN CompetingPages   > 174000001 AND  CompetingPages   <= 176000000 THEN  CompetingPages /51500		
	WHEN CompetingPages   > 176000001 AND  CompetingPages   <= 178000000 THEN  CompetingPages /52000		
	WHEN CompetingPages   > 178000001 AND  CompetingPages   <= 180000000 THEN  CompetingPages /52500		
	WHEN CompetingPages   > 180000001 AND  CompetingPages   <= 182000000 THEN  CompetingPages /53000		
	WHEN CompetingPages   > 182000001 AND  CompetingPages   <= 184000000 THEN  CompetingPages /53500		
	WHEN CompetingPages   > 184000001 AND  CompetingPages   <= 186000000 THEN  CompetingPages /54000		
	WHEN CompetingPages   > 186000001 AND  CompetingPages   <= 188000000 THEN  CompetingPages /54500		
	WHEN CompetingPages   > 188000001 AND  CompetingPages   <= 190000000 THEN  CompetingPages /55000		
	WHEN CompetingPages   > 190000001 AND  CompetingPages   <= 192000000 THEN  CompetingPages /55500		
	WHEN CompetingPages   > 192000001 AND  CompetingPages   <= 194000000 THEN  CompetingPages /56000		
	WHEN CompetingPages   > 194000001 AND  CompetingPages   <= 196000000 THEN  CompetingPages /56500		
	WHEN CompetingPages   > 196000001 AND  CompetingPages   <= 198000000 THEN  CompetingPages /57000		
	WHEN CompetingPages   > 198000001 AND  CompetingPages   <= 200000000 THEN  CompetingPages /57500		
	WHEN CompetingPages   > 200000001 AND  CompetingPages   <= 202000000 THEN  CompetingPages /58000		
	WHEN CompetingPages   > 202000001 AND  CompetingPages   <= 204000000 THEN  CompetingPages /58500		
	WHEN CompetingPages   > 204000001 AND  CompetingPages   <= 206000000 THEN  CompetingPages /59000		
	WHEN CompetingPages   > 206000001 AND  CompetingPages   <= 208000000 THEN  CompetingPages /59500		
	WHEN CompetingPages   > 208000001 AND  CompetingPages   <= 210000000 THEN  CompetingPages /60000		
	WHEN CompetingPages   > 210000001 AND  CompetingPages   <= 212000000 THEN  CompetingPages /60500		
	WHEN CompetingPages   > 212000001 AND  CompetingPages   <= 214000000 THEN  CompetingPages /61000		
	WHEN CompetingPages   > 214000001 AND  CompetingPages   <= 216000000 THEN  CompetingPages /61500		
	WHEN CompetingPages   > 216000001 AND  CompetingPages   <= 218000000 THEN  CompetingPages /62000		
	WHEN CompetingPages   > 218000001 AND  CompetingPages   <= 220000000 THEN  CompetingPages /62500		
	WHEN CompetingPages   > 220000001 AND  CompetingPages   <= 222000000 THEN  CompetingPages /63000		
	ELSE 0
END,
theme_keywords.CMPRankingIndex,
FROM
theme_keywords
WHERE
theme_keywords.profileid = 92

Open in new window

0
 
matthewdacruzAuthor Commented:
HI tigin44

I get the following error
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM
theme_keywords
WHERE
theme_keywords.profileid = 92' at line 125
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
matthewdacruzAuthor Commented:
Hi tinig44
The error was a comma after the theme_keywords.CMPRankingIndex

Is it possible to assign the result to an alias like 'BL' and then round off the result to 0 decimals?

Thanks

Matt
0
 
matthewdacruzAuthor Commented:
Thanks for the help
0
 
tigin44Commented:
here  is what you want
SELECT
theme_keywords.CompetingPages,
ROUND( CASE
	WHEN CompetingPages   < 50000  THEN   CompetingPages/10000		
	WHEN CompetingPages   > 50001 AND  CompetingPages   <= 100000 THEN  CompetingPages /10000		
	WHEN CompetingPages   > 100001 AND  CompetingPages   <= 300000 THEN  CompetingPages /10000		
	WHEN CompetingPages   > 300001 AND  CompetingPages   <= 500000 THEN  CompetingPages /10000		
	WHEN CompetingPages   > 500001 AND  CompetingPages   <= 700000 THEN  CompetingPages /10000		
	WHEN CompetingPages   > 700001 AND  CompetingPages   <= 900000 THEN  CompetingPages /10000		
	WHEN CompetingPages   > 900001 AND  CompetingPages   <= 1100000 THEN  CompetingPages /10000		
	WHEN CompetingPages   > 1100001 AND  CompetingPages   <= 2000000 THEN  CompetingPages /8000		
	WHEN CompetingPages   > 2000001 AND  CompetingPages   <= 4000000 THEN  CompetingPages /8500		
	WHEN CompetingPages   > 4000001 AND  CompetingPages   <= 6000000 THEN  CompetingPages /9000		
	WHEN CompetingPages   > 6000001 AND  CompetingPages   <= 8000000 THEN  CompetingPages /9500		
	WHEN CompetingPages   > 8000001 AND  CompetingPages   <= 10000000 THEN  CompetingPages /10000		
	WHEN CompetingPages   > 10000001 AND  CompetingPages   <= 12000000 THEN  CompetingPages /10500		
	WHEN CompetingPages   > 12000001 AND  CompetingPages   <= 14000000 THEN  CompetingPages /11000		
	WHEN CompetingPages   > 14000001 AND  CompetingPages   <= 16000000 THEN  CompetingPages /11500		
	WHEN CompetingPages   > 16000001 AND  CompetingPages   <= 18000000 THEN  CompetingPages /12000		
	WHEN CompetingPages   > 18000001 AND  CompetingPages   <= 20000000 THEN  CompetingPages /12500		
	WHEN CompetingPages   > 20000001 AND  CompetingPages   <= 22000000 THEN  CompetingPages /13000		
	WHEN CompetingPages   > 22000001 AND  CompetingPages   <= 24000000 THEN  CompetingPages /13500		
	WHEN CompetingPages   > 24000001 AND  CompetingPages   <= 26000000 THEN  CompetingPages /14000		
	WHEN CompetingPages   > 26000001 AND  CompetingPages   <= 28000000 THEN  CompetingPages /14500		
	WHEN CompetingPages   > 28000001 AND  CompetingPages   <= 30000000 THEN  CompetingPages /15000		
	WHEN CompetingPages   > 30000001 AND  CompetingPages   <= 32000000 THEN  CompetingPages /15500		
	WHEN CompetingPages   > 32000001 AND  CompetingPages   <= 34000000 THEN  CompetingPages /16000		
	WHEN CompetingPages   > 34000001 AND  CompetingPages   <= 36000000 THEN  CompetingPages /16500		
	WHEN CompetingPages   > 36000001 AND  CompetingPages   <= 38000000 THEN  CompetingPages /17000		
	WHEN CompetingPages   > 38000001 AND  CompetingPages   <= 40000000 THEN  CompetingPages /17500		
	WHEN CompetingPages   > 40000001 AND  CompetingPages   <= 42000000 THEN  CompetingPages /18000		
	WHEN CompetingPages   > 42000001 AND  CompetingPages   <= 44000000 THEN  CompetingPages /18500		
	WHEN CompetingPages   > 44000001 AND  CompetingPages   <= 46000000 THEN  CompetingPages /19000		
	WHEN CompetingPages   > 46000001 AND  CompetingPages   <= 48000000 THEN  CompetingPages /19500		
	WHEN CompetingPages   > 48000001 AND  CompetingPages   <= 50000000 THEN  CompetingPages /20000		
	WHEN CompetingPages   > 50000001 AND  CompetingPages   <= 52000000 THEN  CompetingPages /20500		
	WHEN CompetingPages   > 52000001 AND  CompetingPages   <= 54000000 THEN  CompetingPages /21000		
	WHEN CompetingPages   > 54000001 AND  CompetingPages   <= 56000000 THEN  CompetingPages /21500		
	WHEN CompetingPages   > 56000001 AND  CompetingPages   <= 58000000 THEN  CompetingPages /22000		
	WHEN CompetingPages   > 58000001 AND  CompetingPages   <= 60000000 THEN  CompetingPages /22500		
	WHEN CompetingPages   > 60000001 AND  CompetingPages   <= 62000000 THEN  CompetingPages /23000		
	WHEN CompetingPages   > 62000001 AND  CompetingPages   <= 64000000 THEN  CompetingPages /23500		
	WHEN CompetingPages   > 64000001 AND  CompetingPages   <= 66000000 THEN  CompetingPages /24000		
	WHEN CompetingPages   > 66000001 AND  CompetingPages   <= 68000000 THEN  CompetingPages /24500		
	WHEN CompetingPages   > 68000001 AND  CompetingPages   <= 70000000 THEN  CompetingPages /25000		
	WHEN CompetingPages   > 70000001 AND  CompetingPages   <= 72000000 THEN  CompetingPages /25500		
	WHEN CompetingPages   > 72000001 AND  CompetingPages   <= 74000000 THEN  CompetingPages /26000		
	WHEN CompetingPages   > 74000001 AND  CompetingPages   <= 76000000 THEN  CompetingPages /26500		
	WHEN CompetingPages   > 76000001 AND  CompetingPages   <= 78000000 THEN  CompetingPages /27000		
	WHEN CompetingPages   > 78000001 AND  CompetingPages   <= 80000000 THEN  CompetingPages /27500		
	WHEN CompetingPages   > 80000001 AND  CompetingPages   <= 82000000 THEN  CompetingPages /28000		
	WHEN CompetingPages   > 82000001 AND  CompetingPages   <= 84000000 THEN  CompetingPages /28500		
	WHEN CompetingPages   > 84000001 AND  CompetingPages   <= 86000000 THEN  CompetingPages /29000		
	WHEN CompetingPages   > 86000001 AND  CompetingPages   <= 88000000 THEN  CompetingPages /29500		
	WHEN CompetingPages   > 88000001 AND  CompetingPages   <= 90000000 THEN  CompetingPages /30000		
	WHEN CompetingPages   > 90000001 AND  CompetingPages   <= 92000000 THEN  CompetingPages /30500		
	WHEN CompetingPages   > 92000001 AND  CompetingPages   <= 94000000 THEN  CompetingPages /31000		
	WHEN CompetingPages   > 94000001 AND  CompetingPages   <= 96000000 THEN  CompetingPages /31500		
	WHEN CompetingPages   > 96000001 AND  CompetingPages   <= 98000000 THEN  CompetingPages /32000		
	WHEN CompetingPages   > 98000001 AND  CompetingPages   <= 100000000 THEN  CompetingPages /32500		
	WHEN CompetingPages   > 100000001 AND  CompetingPages   <= 102000000 THEN  CompetingPages /33000		
	WHEN CompetingPages   > 102000001 AND  CompetingPages   <= 104000000 THEN  CompetingPages /33500		
	WHEN CompetingPages   > 104000001 AND  CompetingPages   <= 106000000 THEN  CompetingPages /34000		
	WHEN CompetingPages   > 106000001 AND  CompetingPages   <= 108000000 THEN  CompetingPages /34500		
	WHEN CompetingPages   > 108000001 AND  CompetingPages   <= 110000000 THEN  CompetingPages /35000		
	WHEN CompetingPages   > 110000001 AND  CompetingPages   <= 112000000 THEN  CompetingPages /35500		
	WHEN CompetingPages   > 112000001 AND  CompetingPages   <= 114000000 THEN  CompetingPages /36000		
	WHEN CompetingPages   > 114000001 AND  CompetingPages   <= 116000000 THEN  CompetingPages /36500		
	WHEN CompetingPages   > 116000001 AND  CompetingPages   <= 118000000 THEN  CompetingPages /37000		
	WHEN CompetingPages   > 118000001 AND  CompetingPages   <= 120000000 THEN  CompetingPages /37500		
	WHEN CompetingPages   > 120000001 AND  CompetingPages   <= 122000000 THEN  CompetingPages /38000		
	WHEN CompetingPages   > 122000001 AND  CompetingPages   <= 124000000 THEN  CompetingPages /38500		
	WHEN CompetingPages   > 124000001 AND  CompetingPages   <= 126000000 THEN  CompetingPages /39000		
	WHEN CompetingPages   > 126000001 AND  CompetingPages   <= 128000000 THEN  CompetingPages /39500		
	WHEN CompetingPages   > 128000001 AND  CompetingPages   <= 130000000 THEN  CompetingPages /40000		
	WHEN CompetingPages   > 130000001 AND  CompetingPages   <= 132000000 THEN  CompetingPages /40500		
	WHEN CompetingPages   > 132000001 AND  CompetingPages   <= 134000000 THEN  CompetingPages /41000		
	WHEN CompetingPages   > 134000001 AND  CompetingPages   <= 136000000 THEN  CompetingPages /41500		
	WHEN CompetingPages   > 136000001 AND  CompetingPages   <= 138000000 THEN  CompetingPages /42000		
	WHEN CompetingPages   > 138000001 AND  CompetingPages   <= 140000000 THEN  CompetingPages /42500		
	WHEN CompetingPages   > 140000001 AND  CompetingPages   <= 142000000 THEN  CompetingPages /43000		
	WHEN CompetingPages   > 142000001 AND  CompetingPages   <= 144000000 THEN  CompetingPages /43500		
	WHEN CompetingPages   > 144000001 AND  CompetingPages   <= 146000000 THEN  CompetingPages /44000		
	WHEN CompetingPages   > 146000001 AND  CompetingPages   <= 148000000 THEN  CompetingPages /44500		
	WHEN CompetingPages   > 148000001 AND  CompetingPages   <= 150000000 THEN  CompetingPages /45000		
	WHEN CompetingPages   > 150000001 AND  CompetingPages   <= 152000000 THEN  CompetingPages /45500		
	WHEN CompetingPages   > 152000001 AND  CompetingPages   <= 154000000 THEN  CompetingPages /46000		
	WHEN CompetingPages   > 154000001 AND  CompetingPages   <= 156000000 THEN  CompetingPages /46500		
	WHEN CompetingPages   > 156000001 AND  CompetingPages   <= 158000000 THEN  CompetingPages /47000		
	WHEN CompetingPages   > 158000001 AND  CompetingPages   <= 160000000 THEN  CompetingPages /47500		
	WHEN CompetingPages   > 160000001 AND  CompetingPages   <= 162000000 THEN  CompetingPages /48000		
	WHEN CompetingPages   > 162000001 AND  CompetingPages   <= 164000000 THEN  CompetingPages /48500		
	WHEN CompetingPages   > 164000001 AND  CompetingPages   <= 166000000 THEN  CompetingPages /49000		
	WHEN CompetingPages   > 166000001 AND  CompetingPages   <= 168000000 THEN  CompetingPages /49500		
	WHEN CompetingPages   > 168000001 AND  CompetingPages   <= 170000000 THEN  CompetingPages /50000		
	WHEN CompetingPages   > 170000001 AND  CompetingPages   <= 172000000 THEN  CompetingPages /50500		
	WHEN CompetingPages   > 172000001 AND  CompetingPages   <= 174000000 THEN  CompetingPages /51000		
	WHEN CompetingPages   > 174000001 AND  CompetingPages   <= 176000000 THEN  CompetingPages /51500		
	WHEN CompetingPages   > 176000001 AND  CompetingPages   <= 178000000 THEN  CompetingPages /52000		
	WHEN CompetingPages   > 178000001 AND  CompetingPages   <= 180000000 THEN  CompetingPages /52500		
	WHEN CompetingPages   > 180000001 AND  CompetingPages   <= 182000000 THEN  CompetingPages /53000		
	WHEN CompetingPages   > 182000001 AND  CompetingPages   <= 184000000 THEN  CompetingPages /53500		
	WHEN CompetingPages   > 184000001 AND  CompetingPages   <= 186000000 THEN  CompetingPages /54000		
	WHEN CompetingPages   > 186000001 AND  CompetingPages   <= 188000000 THEN  CompetingPages /54500		
	WHEN CompetingPages   > 188000001 AND  CompetingPages   <= 190000000 THEN  CompetingPages /55000		
	WHEN CompetingPages   > 190000001 AND  CompetingPages   <= 192000000 THEN  CompetingPages /55500		
	WHEN CompetingPages   > 192000001 AND  CompetingPages   <= 194000000 THEN  CompetingPages /56000		
	WHEN CompetingPages   > 194000001 AND  CompetingPages   <= 196000000 THEN  CompetingPages /56500		
	WHEN CompetingPages   > 196000001 AND  CompetingPages   <= 198000000 THEN  CompetingPages /57000		
	WHEN CompetingPages   > 198000001 AND  CompetingPages   <= 200000000 THEN  CompetingPages /57500		
	WHEN CompetingPages   > 200000001 AND  CompetingPages   <= 202000000 THEN  CompetingPages /58000		
	WHEN CompetingPages   > 202000001 AND  CompetingPages   <= 204000000 THEN  CompetingPages /58500		
	WHEN CompetingPages   > 204000001 AND  CompetingPages   <= 206000000 THEN  CompetingPages /59000		
	WHEN CompetingPages   > 206000001 AND  CompetingPages   <= 208000000 THEN  CompetingPages /59500		
	WHEN CompetingPages   > 208000001 AND  CompetingPages   <= 210000000 THEN  CompetingPages /60000		
	WHEN CompetingPages   > 210000001 AND  CompetingPages   <= 212000000 THEN  CompetingPages /60500		
	WHEN CompetingPages   > 212000001 AND  CompetingPages   <= 214000000 THEN  CompetingPages /61000		
	WHEN CompetingPages   > 214000001 AND  CompetingPages   <= 216000000 THEN  CompetingPages /61500		
	WHEN CompetingPages   > 216000001 AND  CompetingPages   <= 218000000 THEN  CompetingPages /62000		
	WHEN CompetingPages   > 218000001 AND  CompetingPages   <= 220000000 THEN  CompetingPages /62500		
	WHEN CompetingPages   > 220000001 AND  CompetingPages   <= 222000000 THEN  CompetingPages /63000		
	ELSE 0
END, 0) AS BL,
theme_keywords.CMPRankingIndex
FROM
theme_keywords
WHERE
theme_keywords.profileid = 92

Open in new window

0
 
Walter RitzelSenior Software EngineerCommented:
to do that, just apply the round around the whole case statement and then give the alias to the column:
round(case ..... end, 0) as BL.

If this does not work, try to round off on each operation.

Also, as a suggestion, try to reduce the amount of case statements... by what I'm seeing the first four or five statements have the same output.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now