Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

  SUM(placing.points) AS sumpoints,
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.

share|improve this question
Can you post your table structure? Right now, you are not joining the placing and the band tables on any column. You are creating a cartesian result since there is no join between the two tables. – bluefeet Dec 6 '13 at 20:51
DONE! thanks :) – Dude Dec 6 '13 at 20:57
So you are aware that the information is still there right? – Shawn Melton Dec 6 '13 at 21:00
Ah yeah, never mind. – Dude Dec 6 '13 at 21:02
up vote 2 down vote accepted

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:

FROM placing, band;

You are generating data similar to:

|     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:

  SUM(p.points) AS sumpoints,
FROM placing p
  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:

|        92 |       3 |
|        63 |       5 |
|        45 |       4 |
|        12 |       1 |
|         1 |       2 |
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.