CODEWITHSUNDEEP
mysql
user223549
user223549

Reputation: 117

Mysql query with conditional count and value assignment

I have this sample table with Id, Grades:

Id Grades
1   50
1   60
1   70 
1   40
1   80
1   65
2   80
2   67
2   100
2   90
2   60
etc

What I would like to do here is step by step:

Group them like this:

Between     Assigned 
1-50          D 
51-70         C
71-90         B
91-100        A

and if the count of these groups is >=2, then assign the specified value above for each group:

if count(1-50)>=2 then D
if count(51-70)>=2 then C
if count(71-90)>=2 then B
if count(91-100)>=2 then A

so the output will be A,B,C,D or "". grouped by Id. Thank you.

Upvotes: 0

Views: 61

Answers (1)

PeterHe
PeterHe

Reputation: 2766

Try this query:

SELECT a.id, COALESCE(MAX(CASE
   WHEN a.rank='D' AND a.cnt>=2 THEN 'D'
   WHEN a.rank='C' AND a.cnt>=2 THEN 'C'
   WHEN a.rank='B' AND a.cnt>=2 THEN 'B'
   WHEN a.rank='A' AND a.cnt>=2 THEN 'A'
  ELSE NULL
  END),'') AS `rank`
FROM (
  SELECT t.id,r.`rank`,COUNT(*) AS cnt
  FROM (
    SELECT 1 AS low, 50 AS high, 'D' AS `rank`
    UNION ALL
    SELECT 51,70,'C'
    UNION ALL
    SELECT 71,90,'B'
    UNION ALL
    SELECT 91,100,'A') r
  INNER JOIN t t
  ON t.grades>=r.low
  AND t.grades<=r.high
  GROUP BY t.id,r.`rank`) a
GROUP BY a.id

Upvotes: 1

Related Questions