SQL Least Earning Location Count

user_11229655 0
Viewed 11006

I am using an online editor to solve query

https://www.jdoodle.com/execute-sql-online/

have the following SQL Table

create table CITIES(id int, name String);
create table USERS(id int, city_id String, name String, email);
create table RIDES(id int, user_id int, distance int, fare int);

INSERT INTO CITIES (id, name) VALUES
(1,"Cooktown"),
(2,"South Suzanne");

INSERT INTO USERS (id, city_id, name, email) VALUES
(1,2,"a","email"),
(2,2,"b","email"),
(3,1,"c","email"),
(4,1,"d","email"),
(5,1,"e","email"),
(5,1,"f","email");

INSERT INTO RIDES (id, user_id, distance, fare) VALUES
(1,1,21,200),
(2,3,6,55),
(3,2,30,230),
(4,2,16,125),
(5,2,11,110),
(6,6,30,285),
(7,3,18,170),
(8,1,6,50),
(9,2,4,40),
(10,1,10,90),
(11,5,11,95),
(12,5,16,140),
(13,4,24,220),
(14,6,17,160),
(15,2,23,205),
(16,2,11,90),
(17,6,5,50),
(18,3,19,180),
(19,5,22,205),
(20,4,6,60);

Output should be :

South Suzanne 1050

Cooktown 1710

I tried following SQL Query but don't know how to count

select c.name, SUM(r.fare) from CITIES c
  left join USERS u on c.id=u.city_id 
  left join RIDES r on u.id=r.user_id

Getting following output

Cooktown|2705

Any help will be appreciated

sql
1 Answers

Looks like you're missing a GROUP BY:

SELECT c.name, SUM(r.fare) [SumOfFare]
FROM CITIES c
  LEFT JOIN USERS u ON c.id=u.city_id 
  LEFT JOIN RIDES r ON u.id=r.user_id
GROUP BY c.name
ORDER BY SUM(r.fare) ASC;

Also, you might want to add an ORDER BY clause too, so you can see the least earning location at the top.

user_48236040
Related