A few questions about this SQL statement


#1

Here is the example they give us:

select date(ordered_at), /**/
from orders
    /**/ order_items on 
      orders.id = order_items.order_id
group by 1
order by 1;

Here is my attempt:

select date(ordered_at), round(sum(amount_paid), 2)
from orders
	join order_items on 
  orders.id =
order_items.order_id
group by 1
order by 1;

1) What am I doing wrong?

2) What does the "on" keyword do?

3) What is the purpose of the lines "group by 1" and "order by 1"

Thanks


#2

Hey @brendan_whiting,

1) sorry, don't get the problem here

2) With the "On Keyword" you the define two coloumns which contain the same value.
In your example, the "primary key" of your table "orders" is "id".
This "id" also happens to appear in the table "order_items" but not as primary key but as "foreign key".

It basically just tells both tables on which column to join as they have the same information on this column.

3)
"Group By1" - you want to get the revenue on daily basis.
If you didn't use it, it would simply print out the total revenue in one line.

"Order By 1" - your output will be orderd by date.
Starting with the oldest, ending with the latest (01.01.2015-31.12.2015).
Try using "order by 1 desc" which works vice versa. (31.12.2015-01.01.2015)


#3

Cool thanks. My code passed after I refreshed.

In "Group by 1" and "Order by 1", what is the "1" and how does it know?


#4

The "1" refers to the 1. Column. You could also have "order by 2" - then you would order the revenue starting with the smallest.