sql - sum columns from same table having multiple unique ids?

Question

I would like to sum 3 columns from the same table based on three unique id. If id="1" sum(area1) If id="2" sum(area2) if id="3" sum(area3) Table data: id area1 area2 area3 1 10 2 10 1 1 10 10 2 1 10 10 3 1 10 10 3 10 2 10 Output should be: id1= 43 id2 = 21 id3 = 43 Select houseID, sum(area1), b.sum(area2), c.sum(area3) FROM table1, table1 b, table1 c WHERE table1.id="1" or b.id = "2" or c.id="3"

Answer 1

Using Oracle:

Step 1: Create The Table

SQL> CREATE TABLE TABLE1 ( id NUMBER(2,0),

area1 NUMBER(4,0),

area2 NUMBER(4,0),

area3 NUMBER(4,0));

Step 2: Populate The Table With YOUR Data - 5 ROWS

SQL> INSERT INTO TABLE1 VALUES(1, 10, 2, 10);

SQL> INSERT INTO TABLE1 VALUES(1, 1, 10, 10);

SQL> INSERT INTO TABLE1 VALUES(2, 1, 10, 10);

SQL> INSERT INTO TABLE1 VALUES(3, 1, 10, 10);

SQL> INSERT INTO TABLE1 VALUES(3, 10, 2, 10);

SQL> COMMIT;

Step 3: Write The Query Using "GROUP BY"

SQL> SELECT ID, SUM( area1 + area2 + area3) "Sums"

FROM TABLE1

GROUP BY ID

ORDER BY ID;

ID Sums

----- --------

1 43

2 21

3 43

EXPLANATION:

From your problem description, you want the total SUM of all three areas for each ID. So, you want to DISPLAY the ID and the SUM.

If you try a SELECT ID, SUM(area1 + area2 + area3) FROM TABLE1 without a "GROUP BY", you receive an error. I have explained it this way to my Oracle students the past 31 years - that's how much I love this stuff. I've also written 2 Oracle books, and spoken at many Oracle Conferences. It's like you are trying to get Oracle to return the ID column once for every row in the table, and only once for the SUM FUNCTION. It's like leading someone to a round room, and asking them to sit in the corner. Can't be done. So, we throw in the OLD "GROUP BY" CLAUSE AND IT WORKS. Now, the rows are first grouped by ID, and the SUM is returned for each ID. The ORDER BY is just to display the ID's in ascending order.