ID appears twice in query when multiplied with different Prices-CodePudding

I am trying to get the top 5 stations by Sales, but I ran into the problem that one station appears twice if multiplied by a different price.

This is my query:

SELECT distinct b_id, count(t_start_id) * v_preis AS START_PRICE
  FROM bahnhof
 INNER JOIN tickets
    ON t_start_id = b_id
 INNER JOIN connections
    ON t_connection_id = v_id
 GROUP BY b_id, v_preis
 ORDER BY START_PRICE DESC LIMIT 5;

Which gives me the following result:

b_id	START_PRICE
7	75
6	50
4	30
1	16
1	15

What i need though is:

b_id	START_PRICE
7	75
6	50
1	31
4	30

I tried to group by ID only, but it didn't work since v_preis had to be in there too.

The price for 1 is 8 twice and 15 once, so I guess I have a problem with using different rows for one result.

I'm pretty new to SQL, so I'm sorry if this is a dumb question, thank you in advance!

CodePudding user response：

Did you try using SUM() aggregation along with only grouping by id?

SELECT DISTINCT b_id, SUM(v_preis) AS start_price
  FROM bahnhof
  JOIN tickets
    ON t_start_id = b_id
  JOIN connections
    ON t_connection_id = v_id
 GROUP BY b_id
 ORDER BY START_PRICE DESC 
 LIMIT 5;