CODEWITHSUNDEEP
mysqlsql
user1452494
user1452494

Reputation: 1185

Group by 2 columns and sum 2 other columns in MySQL

SQL noob here with a noob question. I have a table like this:

ID   word num_pos num_neg
34   car  1       0
34   car  1       0
100  bus  0       1
123  car  0       1
34   car  0       1
100  bus  1       0
123  bus  0       1
123  car  1       0

What I want is a table that groups all the unique IDs AND words and sums the num_pos and num_neg columns to get the following output:

ID   word num_pos num_neg
34   car  2       1
100  bus  1       1
123  car  1       1
123  bus  0       1

Also, i would like to filter by word, for example for the word 'car', I would need the following table returned:

ID   word num_pos num_neg
34   car  2       1
123  car  1       1

I think it would probably be quicker to query the word first, then group by and sum, but I dont know.

Thank you all in advance!

Upvotes: 0

Views: 82

Answers (3)

Kickstart
Kickstart

Reputation: 21513

SELECT ID, word, SUM(num_pos), SUM(num_neg)
FROM Sometable
GROUP BY ID, word

Upvotes: 0

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Touo must read about GROUP BY (Aggregate) Functions

SELECT ID, word, SUM(num_pos) num_pos, SUM(num_neg) num_neg 
FROM tbl
GROUP BY ID, word

Upvotes: 0

fthiella
fthiella

Reputation: 49049

You just need to use GROUP BY clause on ID and word, and two SUM aggregate functions:

SELECT ID, word, SUM(num_pos) sum_npos, SUM(num_neg) sum_nneg
FROM yourtable
GROUP BY ID, word

Please see fiddle here.

Upvotes: 1

Related Questions