CODEWITHSUNDEEP
sqlsql-server
Khubaib
Khubaib

Reputation: 103

add specific multiple rows (SQL)

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

Upvotes: 1

Views: 61

Answers (4)

Felix Pamittan
Felix Pamittan

Reputation: 31879

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)

Upvotes: 1

shawnt00
shawnt00

Reputation: 17915

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

Upvotes: 0

Kaushik Maheta
Kaushik Maheta

Reputation: 1861

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

Upvotes: 1

tshoemake
tshoemake

Reputation: 1351

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

Upvotes: 1

Related Questions