human
Schema:
member (Member_ID, Card_Number, Name, Hometown, Level)
branch (Branch_ID, Name, Open_year, Address_road, City, membership_amount)
membership_register_branch (Member_ID, Branch_ID, Register_Year)
purchase (Member_ID, Branch_ID, Year, Total_pounds)
Please answer with SQL.
assistant
Sure, I understand.
human
How many purchases are there in 2018?
assistant
SELECT COUNT( * ) FROM purchase WHERE year = 2018;
human
How many of them were made through any branch in London?
assistant
SELECT COUNT( * ) FROM purchase AS T1 JOIN branch AS T2 ON T1.branch_id = T2.branch_id WHERE T2.city = 'London' AND T1.year = 2018;
human
Show me the sum of total pounds of them.
assistant
SELECT SUM( total_pounds ) FROM purchase AS T1 JOIN branch AS T2 ON T1.branch_id = T2.branch_id WHERE T2.city = "London" AND T1.year = 2018 ;