I'm anxious for a question !!

7058707

There is a table A
ID GX NUM NUM1
1 A 100 50
2 A 200 30
3 A 300 40
3 B 250 20
4 B 100 30
5 B 200 20
5 A 150 25
6 B 200 30
Now we need to sum the values ​​of NUM and NUM1 by ID. If the ID is the same, GX has two records of A and B.
The value of NUM is taken as the value of A, and the value of NUM1 is taken as the sum of A and B. They are grouped by ID.

116657396

select ID, sum (case GX when 'A' then NUM else 0 end) as NUM, sum (NUM1) as NUM1 from A group by ID

cv4bf5d65fd

select ID, GX, sum (NUM) num, sum (NUM1) num1
from tablename
group by ID, GX

116657396

select ID, sum (case GX when 'A' then NUM else 0 end) as NUM, sum (NUM1) as NUM1 from A where exists (select 1 from A t where t.ID = ID and t.GX = 'A' ) group by ID
union
select ID, sum (NUM) as NUM, sum (NUM1) as NUM1 from A where not exists (select 1 from A t where t.ID = ID and t.GX = 'A') group by ID

sinexe

select id, sum (case when gx = 'A' then num else 0 end), sum (num + num1)
from (
    select id, gx, sum (num), sum (num1)
    from t
    group by id, gx) _x
group by id

cv4bf5d65fd

create table a (id int, GX char (1), NUM int, NUM1 int)
insert a
select 1, 'A', 100, 50
union all
select 2, 'A', 200, 30
union all
select 3, 'A', 300, 40
union all
select 3, 'B', 250, 20
union all
select 4, 'B', 100, 30
union all
select 5, 'B', 200, 20
union all
select 5, 'A', 150, 25
union all
select 6, 'B', 200, 30
union all
select 6, 'B', 200, 30
union all
select 6, 'B', 200, 30



select ID, GX, sum (NUM) num, sum (NUM1) num1
from a
group by ID, GX

wgfxman

CREATE TABLE A
(ID INT,
GX CHAR (1),
NUM INT,
NUM1 INT
)
GO

INSERT INTO A (ID, GX, NUM, NUM1)
    SELECT 1, 'A', 100,50
UNION ALL SELECT 2, 'A', 200,30
UNION ALL SELECT 3, 'A', 300,40
UNION ALL SELECT 3, 'B', 250,20
UNION ALL SELECT 4, 'B', 100,30
UNION ALL SELECT 5, 'B', 200,20
UNION ALL SELECT 5, 'A', 150,25
UNION ALL SELECT 6, 'B', 200,30
GO

SELECT ID, MAX (ISNULL (NNUM, NUM)), SUM (NUM1) FROM
(SELECT M.ID, M.GX, M.NUM, M.NUM1, N.GX AS NGX, N.NUM AS NNUM FROM AM LEFT JOIN AN ON M.ID = N.ID AND N.GX = 'A' ) T
GROUP BY ID

7058707

It seems that the records with only A or B have not been calculated yet.

wgfxman

SELECT ID, ISNULL (NNUM, NUM), SUM (NUM1) FROM
(SELECT M.ID, M.GX, M.NUM, M.NUM1, N.GX AS NGX, N.NUM AS NNUM FROM AM LEFT JOIN AN ON M.ID = N.ID AND N.GX = 'A' ) T
GROUP BY ID, ISNULL (NNUM, NUM)
ORDER BY ID

This can also

wgfxman

You try my two