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!
Advertisement
Answer
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;