ID appears twice in query when multiplied with different Prices

December 19, 2021

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 tied 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!

>Solution :

Did you try using SUM() and only group by id?

SELECT distinct
b_id,
SUM(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
ORDER BY START_PRICE DESC
LIMIT 5;