1

I have a table name temp with column customer_id , sum and I need to add extra column customer_label (may be on view) which is 1 if customer lies in top 10% according to value of sum, 2 if lies within 10%-20%, 3 if lies within 20%-60%, 4 if lies within 60-100%. How can I do this?

Some sample data is included at sqlfiddle

4
  • sqlfiddle.com/#!2/9e8e0/5/1
    – nKandel
    May 25, 2013 at 8:05
  • 1
    So, for 50 customers, the Top 5 should have label=1, the next 5 label=2, the next 20 lable=3, etc.? May 25, 2013 at 8:16
  • @ypercube yap... but this 50 customer is just for test case... Data row may vary...
    – nKandel
    May 25, 2013 at 13:35
  • @ypercube could you help me to write the query?
    – nKandel
    May 25, 2013 at 16:24

2 Answers 2

1
select customer_id,sum,case 
when pct_row<=0.10 then 1
when pct_row>0.10 and pct_row<=0.20 then 2
when pct_row>0.20 and pct_row<=0.60 then 3
when pct_row>0.60 then 4
end as customer_label from (
select customer_id,sum,(@curRow := @curRow+1)/c as pct_row
from temp t 
jOIN (SELECT @curRow := 0) r
JOIN (SELECT @curRow2 := 0) r2 
join (select count(*) c from temp) s
order by sum desc) p;

sqlfiddle :- here

I don't know whether this is efficient method or not but work fine for small data set.

0

WITH CTE (CUSTOMER_ID,SUM,ROW_NUMBERS,CUSTOMER_LABER )
AS
(
SELECT TOP(10) PERCENT WITH TIES A.customer_id,A.SUM ,
ROW_NUMBER()OVER (ORDER BY CUSTOMER_ID) ROW_NUMBERS ,
CASE WHEN CUSTOMER_ID BETWEEN 0 AND 4 THEN '1' ELSE NULL END CUSTOMER_LABER
FROM PERCENTS A
ORDER BY A.CUSTOMER_ID
),
CTE2 (CUSTOMER_ID,SUM,ROW_NUMBERS,CUSTOMER_LABER )
AS
(

SELECT TOP(20) PERCENT WITH TIES A.customer_id,A.SUM , ROW_NUMBER()OVER (ORDER BY CUSTOMER_ID) ROW_NUMBERS , CASE WHEN CUSTOMER_ID BETWEEN 6 AND 10 THEN '2' ELSE NULL END CUSTOMER_LABER FROM PERCENTS A ORDER BY A.CUSTOMER_ID ), CTE3 (CUSTOMER_ID,SUM,ROW_NUMBERS,CUSTOMER_LABER ) AS ( SELECT TOP(60) PERCENT WITH TIES A.customer_id,A.SUM , ROW_NUMBER()OVER (ORDER BY CUSTOMER_ID) ROW_NUMBERS , CASE WHEN CUSTOMER_ID BETWEEN 11 AND 30 THEN '3' ELSE NULL END CUSTOMER_LABER FROM PERCENTS A ORDER BY A.CUSTOMER_ID ),

CTE4 (CUSTOMER_ID,SUM,ROW_NUMBERS,CUSTOMER_LABER ) AS( SELECT TOP(100) PERCENT WITH TIES A.customer_id,A.SUM , ROW_NUMBER()OVER (ORDER BY CUSTOMER_ID) ROW_NUMBERS , CASE WHEN CUSTOMER_ID BETWEEN 30 AND 50 THEN '4' ELSE NULL END CUSTOMER_LABER FROM PERCENTS A ORDER BY A.CUSTOMER_ID ) SELECT * FROM CTE WHERE CUSTOMER_LABER IS NOT NULL UNION ALL SELECT * FROM CTE2 WHERE CUSTOMER_LABER IS NOT NULL UNION ALL SELECT * FROM CTE3 WHERE CUSTOMER_LABER IS NOT NULL UNION ALL SELECT * FROM CTE4 WHERE CUSTOMER_LABER IS NOT NULL

3
  • 1
    MySQL does not have TOP. No CTEs either. No ROW_NUMBER() function either. May 25, 2013 at 13:36
  • Ohh sorry I have not seen that your question is from MySql May 25, 2013 at 14:57
  • see the Tag ...
    – nKandel
    May 25, 2013 at 15:33

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.