Clever Geek Handbook

Manipulating an SQL Query to Create Data Categories that Return Maximum Values

I have a table that looks like this:

Day  Limit  Price  
1   52      0.3  
1   4       70  
1   44      200  
1   9       0.01   
1   0       0.03  
1   0       0.03  
2   52      0.4  
2   10      70  
2   44      200      
2   5       0.01   
2   0       0.55   
2   2       50

Is there a way that I can use SQL to process the result in a table with different categories for price and select a maximum value for the limit corresponding to its price?

Day   0-10   10-100     100+  
 1     52       4        44  
 2     52      10        44
+3


source to share


2 answers


You can use CASE

and MAX

:

SELECT Day,
  MAX(CASE WHEN Price BETWEEN 0 AND 10 THEN Limit ELSE 0 END) as ZeroToTen,
  MAX(CASE WHEN Price BETWEEN 10 AND 100 THEN Limit ELSE 0 END) as TenToHundred,
  MAX(CASE WHEN Price > 100 THEN Limit ELSE 0 END) as HundredPlus
FROM YourTable
GROUP BY Day

Here is the Fiddle .



BTW - if you are using MySQL add checkmarks around LIMIT as this is the keyword.

Good luck.

+2


source


I'm not sure if you are limited to 0-10 10-100 and 100+, but I tried to create a SQL statement, but it looks like it would be very difficult without a stored procedure unless you are limited to 4, otherwise you need the SQL statement to execute loop or while.



If you just want to stick with only 4 columns then I agree with the below answer using CASE and MAX

0


source






More articles:


All Articles