SQL: Give each group same value when match condition-CodePudding

I have a table below, I want to set condition for each group, and if that group pass the condition then give a fixed number to all the records in that group.

In this case, each class is a group, if the sum score of group >= 1 then I want to give a fixed number 1 to all the record in that group (in new column "score")

In class A, their sum score => 0 1 0 = 1, then pass the condition.

In class C, their sum score => 0 0 = 0, then not pass the condition, give 0 to all the record in that group.

Name	class	score
Allen	A	0
Sarah	A	1
Derek	A	0
Josh	B	1
Pini	B	0
Karen	C	0
Louis	C	0

The result should be like below.

Name	class	score	pass
Allen	A	0	1
Sarah	A	1	1
Derek	A	0	1
Josh	B	1	1
Pini	B	0	1
Karen	C	0	0
Louis	C	0	0

I'm thinking of using window function but not sure how to achieve that. Thanks in advance!

CodePudding user response：

In case of grouping rows based on any aggregate functions,sub queries as well as joins can be used. here I've written both the queries and shared the execution cost of both.

In the give case and data, sub queries is more efficient compare to join.

using JOIN

select * from tempTable t1 join (select class, sum(score) from tempTable  group by class ) as t2 on t2.Class = t1.Class;

using SubQuery

select *, (select sum(score) from tempTable t1 where t1.class = t2.class group by class) from tempTable t2;

thanks !

CodePudding user response：

You may use SUM() as a window function here:

SELECT Name, class, score,
       CASE WHEN SUM(score) OVER (PARTITION BY class) >= 1
            THEN 1 ELSE 0 END AS pass
FROM yourTable
ORDER BY class, Name;

CodePudding user response：

In case you are using lower version of mysql

CREATE TABLE tempTable(
Name VARCHAR(50) NOT NULL,
Class VARCHAR(100) NULL,
Score INT(50) NULL
);
INSERT INTO tempTable(Name, Class, Score)Values('Allen', 'A', 0);
INSERT INTO tempTable(Name, Class, Score)Values('Sarah', 'A', 1);
INSERT INTO tempTable(Name, Class, Score)Values('Derek', 'A', 0);
INSERT INTO tempTable(Name, Class, Score)Values('Josh', 'B', 1);
INSERT INTO tempTable(Name, Class, Score)Values('Pinni', 'B', 0);
INSERT INTO tempTable(Name, Class, Score)Values('Karen', 'C', 0);
INSERT INTO tempTable(Name, Class, Score)Values('Louis', 'C', 0);

SELECT T1.Name, T1.Class, T1.Score, T2.Pass 
FROM tempTable T1 INNER JOIN 
(SELECT Class, SUM(Score) Pass
FROM tempTable
Group BY Class) T2
ON T1.Class = T2.Class ;