SQL: enter SUM value

Question

I have a table with sometable:

name |test_date | score ----------------------- jon |2012:07:01| 95 jon |2012:07:01| 60 jon |2012:07:01| 30 alex |2012:07:01| 80 alex |2012:07:01| 85 alex |2011:05:01| 40 emile|2011:01:01| 89 

I want to get a few lines for each name and provide information about its score_grading with the rules: rating> 79 = A, 80> Score> 49 = B otherwise C.

The problem is what I want: if on the same day there will be more than one level of points for the student, then he will be considered as one score_grade. For example, in the table, we see that alex get A twice in one day, I want it to be considered only 1 A.

Thus, the result will be

 name | A | B | C jon | 1 | 1 | 1 alex | 1 | 0 | 1 emily| 1 | 0 | 0 

I only know the code:

 SELECT name, SELECT SUM(IF(score)>79),1,0)) as A, SELECT SUM(IF(80>score>49),1,0)) as B, SELECT SUM(IF(score)<50),1,0)) as C from sometable group by name 

Nha, how can I put SEND on it? Can anyone give a solution? Maybe he does not need DISTINCT? Thank you ^^

Answer 1

Do you mean something like the following ( sqlfiddle )?

 SELECT name, SUM(IF(A>=1,1,0)) as A, SUM(IF(B>=1,1,0)) as B, SUM(IF(C>=1,1,0)) as C FROM ( SELECT name, test_date, SUM(IF(score>79,1,0)) as A, SUM(IF(score BETWEEN 49 AND 79,1,0)) as B, SUM(IF(score<50,1,0)) as C FROM sometable GROUP BY name, test_date ) daygroups GROUP BY name 

This first shuffles your data into the name, test_date, A, B, C lines in the subquery. Then the external query will aggregate these rows, taking 1 if that day there is at least one point of this class, otherwise it takes 0.

---

This should work too ( sqlfiddle ):

 SELECT name, SUM(IF(lettergrade = 'A',1,0)) AS A, SUM(IF(lettergrade = 'B',1,0)) AS B, SUM(IF(lettergrade = 'C',1,0)) AS C FROM ( SELECT DISTINCT name, test_date, CASE WHEN score>79 THEN 'A' WHEN score BETWEEN 49 AND 79 THEN 'B' ELSE 'C' END AS lettergrade FROM sometable ) lettergrades GROUP BY name 

I'm not sure which one will be better. This query uses DISTINCT , as you suggest in your question. First, it resolves each numerical score to the corresponding letter column, then DISTINCT displays duplicates. Finally, it shuffles the data into columns.

Answer 2

This should work:

 SELECT name, SUM(IF(score > 79, 1, 0)) as A, SUM(IF(score BETWEEN 50 AND 79, 1, 0)) as B, SUM(IF(score < 50, 1, 0)) as C FROM (SELECT DISTINCT name, score FROM sometable) a GROUP BY name;