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.

This exercise is part of the course

Hierarchical and Recursive Queries in SQL Server

View Course

Exercise 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.

Hands-on interactive exercise

Have a go at this exercise by completing this sample 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.___;

Edit and Run Code