# How to add up a column depending on data from another column

Posted on

### Question :

I have a table which has results and a table that has `band_name` and `band_id`.

Now I need to create a query which will add up the total points for each band and display the band name and ID, so far I have

``````SELECT
SUM(placing.points) AS sumpoints,
band.band_id
FROM placing, band
GROUP BY band.band_id
ORDER BY SUM(placing.points) DESC
``````

The results end up giving me each unique `band_id` but the total of all the points on all bands as oppose to the bands points.

The problem is that you are not joining your two tables based on `band_id`, as a result you are creating a Cartesian product. When you create this cartesian product, you are combining each row from your `placing` table to each row in the `band` table.

For example, if you have data similar to the following:

``````create table band
(
band_id number
);

insert into band values (1);
insert into band values (2);
insert into band values (3);
insert into band values (4);
insert into band values (5);

create table placing
(
band_id number,
points number
);

insert into placing values (1, 10);
insert into placing values (1, 2);
insert into placing values (2, 1);
insert into placing values (3, 3);
insert into placing values (3, 89);
insert into placing values (4, 45);
insert into placing values (5, 63);
``````

And you query the data without a join on the `band_id` column:

``````SELECT
placing.points,
band.band_id
FROM placing, band;
``````

You are generating data similar to:

``````| POINTS | BAND_ID |
|--------|---------|
|     10 |       1 |
|      2 |       1 |
|      1 |       1 |
|      3 |       1 |
|     89 |       1 |
|     45 |       1 |
|     63 |       1 |
``````

See Demo. As you can see `band_id = 1` now has every single `point` value from the `placing` table even though your really only have `points = 10` and `points=2`.

In order to get the correct result, you need to `JOIN` the two tables on the `band_id` column. Your query will be similar to the following:

``````SELECT
SUM(p.points) AS sumpoints,
b.band_id
FROM placing p
INNER JOIN band b
on p.band_id = b.band_id
GROUP BY b.band_id
ORDER BY SUM(p.points) DESC;
``````

See SQL Fiddle with Demo. This will give a result of:

``````| SUMPOINTS | BAND_ID |
|-----------|---------|
|        92 |       3 |
|        63 |       5 |
|        45 |       4 |
|        12 |       1 |
|         1 |       2 |
``````