[Solved] Counting number of zeros in MySQL [closed]

by


I stripped the trailing and leading spaces from your text-formatted data and created an equivalent sample schema using SQL Fiddle. The setup looks like this:

CREATE TABLE Grades
    (`htno` int, `sub` varchar(1), `marks` int, `credits` int)
;

INSERT INTO Grades
    (`htno`, `sub`, `marks`, `credits`)
VALUES
    (1, 'a', 15, 0),
    (1, 'b', 10, 0),
    (1, 'c', 25, 4),
    (1, 'd', 24, 4),
    (1, 'e', 22, 2),
    (1, 'f', 12, 0),
    (2, 'a', 22, 4),
    (2, 'b', 15, 0),
    (2, 'c', 23, 4),
    (2, 'd', 18, 2),
    (2, 'e', 20, 4),
    (2, 'f', 6, 0),
    (3, 'a', 22, 4)
;

I have made the following assumptions:

  • Each row represents the attainment of a student in a subject.
  • The “halticket number” unique identifies each student and is represented by the column htno.
  • A value of zero in the credits column represents a subject in the student’s “backlog”.
  • A value greater than zero in the credits column represents a “passed subject”.

This query meets your requirements:

SELECT
  SUM(CASE WHEN credits = 0 THEN 1 ELSE 0 END) AS `Number of backlogs`,
  SUM(CASE WHEN credits > 0 THEN 1 ELSE 0 END) AS `Passed subjects`
FROM Grades
WHERE htno = 1;

We SELECT from the Grades table all the rows for the student with halticket number 1 using a WHERE filter.

The first CASE expression computes a new column that contains a 0 if credits is non-zero and contains a 1 otherwise.

The second CASE expression computes a new column that contains a 0 if credits is non-positive and contains a 1 otherwise.

You can imagine that the intermediate result would look like this:

[Case expression 1] [Case expression 2]
0   1
0   1
1   0
1   0
1   0
0   1

We treat the entire result set as a group for aggregation, and add up all the values in the first and second computed columns using the SUM function.

The sum of the first column is 3, and the sum of the second column is 3.

This gives you the final result set.

You can experiment with my interactive solution on SQL Fiddle.

0

solved Counting number of zeros in MySQL [closed]