Sql data filtering-CodePudding

I am begginer to sql , need a help, how can i segregate the marks of a student only when the pass flag is Y . currently for learning purpose , i have created a table and added 3 columns with two Y flag and one N flag. The query should only return sum of mark only when all the flag is Y.

Can someone help .

Thanks,

CodePudding user response：

You can use a HAVING clause and conditional aggregation:

SELECT name,
       SUM(marks)
FROM   table_name
GROUP BY name
HAVING COUNT(CASE WHEN pass = 'N' THEN 1 END) = 0

Which, for the sample data:

CREATE TABLE table_name (
  name  VARCHAR(10),
  marks NUMERIC,
  pass  VARCHAR(1)
);

INSERT INTO table_name (name, marks, pass)
SELECT 'xxx', 50, 'Y' UNION ALL
SELECT 'xxx', 50, 'Y' UNION ALL
SELECT 'zzz', 50, 'N';

Outputs the "sum of mark only when all the flag is Y.":

name SUM(marks)

xxx 100

name	SUM(marks)
xxx	100

db<>fiddle here

CodePudding user response：

Filter the value you want in the WHERE clause

Example: SELECT SUM(Mark) FROM TABLE WHERE PASS = 'Y'