add specific multiple rows (SQL)

Question

I am new to SQL and I want to write a query to add multiple rows in a table.

For example:

Table:

matchid|player1id|player2id|player1score|player2score
101    |20       |10       |0           |100
101    |20       |10       |0           |100
101    |20       |10       |0           |100
201    |20       |10       |645         |0
201    |20       |10       |100         |700
201    |20       |10       |0           |100

Required output:

matchid|player1id|player2id|player1score|player2score
101    |20       |10       |0           |300
201    |20       |10       |745         |800

Note: I have to do this without using GROUP BY

SELECT matchid, player1id, player2id, player1score = SUM(player1score), player2score = SUM(player2score) FROM yourTable GROUP BY matchid, player1id, player2id — Felix Pamittan
– Felix Pamittan, Commented Dec 3, 2015 at 5:27

Felix Pamittan · Accepted Answer · 2015-12-03 05:46:38Z

1

Without using GROUP BY:

SELECT *
FROM (
    SELECT DISTINCT matchid, player1id, player2id FROM tbl
) AS t
CROSS APPLY(
    SELECT
        SUM(player1score), SUM(player2score)
    FROM tbl
    WHERE
        matchid = t.matchid
        AND player1id = t.player1id
        AND player2id = t.player2id
) AS x(player1score, player2score)

answered Dec 3, 2015 at 5:46

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

tshoemake · Accepted Answer · 2015-12-03 05:35:43Z

1

  select matchid,player1id,player2id,SUM(player1score) as 
player1score,SUM(player2score) as player2score 
FROM table1
Group by player1id,player2id, matchid

answered Dec 3, 2015 at 5:35

tshoemake

1,3511 gold badge17 silver badges33 bronze badges

1 Comment

Khubaib Over a year ago

thanks for the response tshoemake. Sorry, I forgot to mention that i dont want to use group by clause.

marc_s · Accepted Answer · 2015-12-03 05:44:31Z

1

SELECT
    matchid, player1is, player2id,
    SUM(player1score) as player1score,
    SUM(player2score) as player2score 
FROM 
    tablename 
GROUP BY
    matchid, player1id, player2id

edited Dec 3, 2015 at 5:44

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Dec 3, 2015 at 5:28

Kaushik Maheta

1,9511 gold badge20 silver badges27 bronze badges

1 Comment

Khubaib Over a year ago

thanks for the response kaushik. Sorry, I forgot to mention that i dont want to use group by clause.

shawnt00 · Accepted Answer · 2015-12-03 05:45:35Z

0

Does this satisfy the requirement?:

select
    matchid, player1id, player2id,
    (select sum(player1score from Table t2 where t2.matchid = t.matchid) as player1score,
    (select sum(player2score from Table t2 where t2.matchid = t.matchid) as player2score
from
    (select distinct matchid, player1id, player2id from Table) t

answered Dec 3, 2015 at 5:45

shawnt00

18.1k3 gold badges23 silver badges25 bronze badges

Collectives™ on Stack Overflow

add specific multiple rows (SQL)

4 Answers 4

Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related