SQL change the order of one column

1

Here is my code snippet.
What I wanna achieve is that to make the order of the price column going from 1000000 to null.
so to basically reverse the original sequence.

Okay I posted the wrong question the 1st time, what I need to know is how to select the table and make it looks like this:

name | email | money_total_spent_perperson
Doctor Who|doctorwho@timelords.com | 1001000
Harry Potter|harry@potter.com |40
Captain Awesome|captain@awesome.com | NULL

Here is the original table:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    email TEXT);
    
INSERT INTO customers (name, email) VALUES ("Doctor Who", "doctorwho@timelords.com");
INSERT INTO customers (name, email) VALUES ("Harry Potter", "harry@potter.com");
INSERT INTO customers (name, email) VALUES ("Captain Awesome", "captain@awesome.com");

CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER,
    item TEXT,
    price REAL);

INSERT INTO orders (customer_id, item, price)
    VALUES (1, "Sonic Screwdriver", 1000.00);
INSERT INTO orders (customer_id, item, price)
    VALUES (2, "High Quality Broomstick", 40.00);
INSERT INTO orders (customer_id, item, price)
    VALUES (1, "TARDIS", 1000000.00);
    
select customers.name,customers.email,orders.item,orders.price
from customers
left outer join orders
on customers.id = orders.customer_id
;

how am I gonna do that?

3

okay, here is the requirements about this snippet:

/* Now, create another query that will result in one row per each customer, with their name, email, and total amount of money they’ve spent on orders. Sort the rows according to the total money spent, from the most spent to the least spent. */

If I understand it correctly, the output should be something like this:

name | email | money_total_spent_perperson
Doctor Who|doctorwho@timelords.com | 1001000
Harry Potter|harry@potter.com |40
Captain Awesome|captain@awesome.com | NULL

if I use sum() it will give me the total price of everyone’s purchase
how do I let it calculate only doctor who’s money_total_spent ?

5

wait what??

what you mean by withdrawn by author?

6

I misread your question and deleted my answer - I am checking now to give you a proper one :wink:

7

oh okay okay, I think I need to readdress my question.,
cos the 1st time I post it, I did not quite understand what the SQL question really is.

8

You need a GROUP BY clause grouping on name and email with the sum on price

SELECT name, email, SUM(price) as price
FROM customers
LEFT OUTER JOIN orders
ON customers.id = orders.customer_id
GROUP BY name, email
ORDER BY price DESC
1 Like
9

thank you! :grinning: