Getting the number of orders & total costs

In this exercise, you will JOIN two tables to get the total number of orders for each person and the sum of prices of all orders. You have to join the Person and History tables on the primary and foreign keys to get all required information.

Cet exercice fait partie du cours

Hierarchical and Recursive Queries in SQL Server

Afficher le cours

Instructions

COUNT() the number of orders and alias it as Orders.
SUM() the total price of all orders and alias it as Costs.
Join the Person and History tables.
Aggregate the information on ID using GROUP BY.

Exercice interactif pratique

Essayez cet exercice en complétant cet exemple de code.

INSERT INTO Person  
VALUES (1, 'Andrew', 'Anderson','Union Ave 10','New York','1986-12-30');
INSERT INTO Person 
VALUES (2, 'Sam', 'Smith','Flushing Ave 342','New York','1986-12-30');

INSERT INTO History VALUES ( 1, 'IPhone XS', 1000, 1);
INSERT INTO History VALUES ( 2, 'MacBook Pro', 1800, 1);
INSERT INTO History VALUES ( 5, 'IPhone XR', 600, 2);
INSERT INTO History VALUES ( 6, 'IWatch 4', 400, 1);

SELECT 
    Person.ID,
    -- Count the number of orders
    ___(___) as ___,
    -- Add the total price of all orders
    ___(___) as ___
FROM Person
	-- Join the tables Person and History on their IDs
	___ History 
	ON Person.___ = History.___
-- Aggregate the information on the ID
___ ___ Person.___;

Modifier et exécuter le code