SQLite Relational Database Query in Ruby

Question

I have 2 SQLite databases, Salesmen and Sales. Here's what the original CSV files looked like, but I've already put them in SQLite (just so you can see how the tables are layed out):

Salesmen Table
id, name
1, john
2, luther
3, bob

Sales Table
id, salesmen_id, sales_amount
1, 1, 100
2, 3, 20
3, 2, 35
4, 3, 25
5, 1, 55
6, 2, 200
7, 2, 150

My question is how do I write a function in ruby that will return all the Salesmen names, sorted by their total sales amount? I know this requires using a join, but I'm not entirely sure how the query should look like.

I want the new table to look like this:

New Table
name, total_sales
luther, 385
john, 155
bob, 45

The new sqlite query should be in this format:

$db.execute %q{
    SELECT account_name, units, unit_price 
    FROM accounts, positions
    ...
}

Thanks in advance

sorry i said 2 sqlite databases... I meant 2 tables in the SAME sqlite database — bcan001
– bcan001, Commented Sep 1, 2015 at 21:09

yez · Accepted Answer · 2015-09-01 22:01:07Z

1

I think this is what you want

SELECT name, sum(sales_amount)
FROM salesmen INNER JOIN sales on sales.salesmen_id = salesmen.id
GROUP BY salesmen_id

answered Sep 1, 2015 at 22:01

yez

2,37811 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQLite Relational Database Query in Ruby

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related