1

I want to add the total marks of different three table in my database and find the number of students whose marks is less than 80. So I did it this way:

SELECT 
    (SELECT SUM((totalmarks / 30) * 5) AS marks1 FROM marks) +
    (SELECT SUM((totalmarks / 25) * 5) AS marks2 FROM marks2) +
    (SELECT SUM((totalmarks / 15) * 5) AS marks3 FROM marks3) AS result
HAVING COUNT((result / 300) * 50) < 80

I am able to get the sum of the marks, but when I put HAVING COUNT condition, it shows nothing. Can someone tell me how to get the number of student using COUNT?

It shows error:

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 'HAVING COUNT((result/300)*50)>80' at line 11 .

Appreciate if someone can help me with this.

enter image description here

Improve this question
4
  • You should group your results by student. What is the field for the student ID in your tables? Commented Nov 22, 2017 at 7:30
  • add the table definition Commented Nov 22, 2017 at 7:33
  • 2
    Count counts the number of rows in your resultset, not the value of the result. select count(1 + 1 +20); gives 1 not 22 Commented Nov 22, 2017 at 7:34
  • @trincot thanks, the field for student ID is stuID. Commented Nov 22, 2017 at 7:51

2 Answers 2

Reset to default
1

You need to group your results by student:

select studID, sum(result) * 5 as result
from (
    select studID, totalmarks / 30 as result from marks1
    union all
    select studID, totalmarks / 25 as result from marks2
    union all
    select studID, totalmarks / 15 as result from marks3
) as base
group by studID
having  ((result / 300) * 50) < 80

NB: it is a bit strange how you divide and multiply. For example, why not the equivalent:

having  result < 480

If the logic is that in marks1 the highest possible score is 30, for marks2 25 and for marks3 15, and you want to give each of the three an equal weighting, then indeed you must divide each of the totalmarks as you do.

After the multiplication with five, this would mean the result can never be more than five.

So the having test is then quite superfluous: all results will be below 480.

So maybe you wanted to see who did not have the perfect score, for which the having clause should then be:

having result < 5
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks sir for your answer.
Sir,how if I only want the number of student using COUNT? I mean it will only show how many student get marks less than than 480 for instance, it show result=18
I noticed you removed the acceptance flag. Was there an issue?
0

Using your current query, it needs a reference table/container to assess the HAVING condition.. so, here's my suggested solution:

SELECT result
FROM (
    SELECT (
        SELECT SUM(( totalmarks/30 )*5) AS marks1
        FROM marks
    ) + ( 
        SELECT SUM(( totalmarks/25 )*5) AS marks2
        FROM marks2 
    ) + ( 
        SELECT SUM(( totalmarks/15 )*5) AS marks3
        FROM marks3 
    ) AS `result` 
) AS `derived`
HAVING COUNT((result / 300) * 50) < 80
Improve this answer

1 Comment

@Lawrence I think you need to the question your structure for the tables being used, so that we.. people of SO can further help you

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.