Use the Customer TableThis query will need to join the Customer table to the Orders Table by CustomerIDThis query will need to join the Orders table to the OrderItems Table by OrderIDDisplay this content:Display the Email address,Sum of ItemPrice * Quantity as 'ItemPriceTotal'Calculate the DiscountAmount * Quantity as "Discount Amount Total"Group this output by EmailAddressSort the data using the ItemPriceTotal in descending order.
Question
Use the Customer TableThis query will need to join the Customer table to the Orders Table by CustomerIDThis query will need to join the Orders table to the OrderItems Table by OrderIDDisplay this content:Display the Email address,Sum of ItemPrice * Quantity as 'ItemPriceTotal'Calculate the DiscountAmount * Quantity as "Discount Amount Total"Group this output by EmailAddressSort the data using the ItemPriceTotal in descending order.
Solution
Here is a SQL query that should give you the desired output:
SELECT
c.Email AS 'EmailAddress',
SUM(oi.ItemPrice * oi.Quantity) AS 'ItemPriceTotal',
SUM(oi.DiscountAmount * oi.Quantity) AS 'Discount Amount Total'
FROM
Customer c
JOIN
Orders o ON c.CustomerID = o.CustomerID
JOIN
OrderItems oi ON o.OrderID = oi.OrderID
GROUP BY
c.Email
ORDER BY
'ItemPriceTotal' DESC;
Here's a step-by-step breakdown of the query:
-
SELECT c.Email AS 'EmailAddress', SUM(oi.ItemPrice * oi.Quantity) AS 'ItemPriceTotal', SUM(oi.DiscountAmount * oi.Quantity) AS 'Discount Amount Total': This part of the query is selecting the columns we want to display in the final output. We're displaying the email address from the Customer table, and the total item price and total discount amount from the OrderItems table. -
FROM Customer c JOIN Orders o ON c.CustomerID = o.CustomerID JOIN OrderItems oi ON o.OrderID = oi.OrderID: This part of the query is joining the three tables together. We're joining the Customer table to the Orders table on the CustomerID, and then joining the Orders table to the OrderItems table on the OrderID. -
GROUP BY c.Email: This part of the query is grouping the output by the email address. This means that for each unique email address, we'll get one row in the output with the total item price and total discount amount for that email. -
ORDER BY 'ItemPriceTotal' DESC: This part of the query is sorting the output by the total item price in descending order. This means that the email addresses with the highest total item price will be at the top of the output.
Similar Questions
Create a view named purchase_amount_view to calculate the total purchase amount for each order from the provided table orderdetails.Table Details:Table name: orderdetails (table names are case sensitive)Column Names:order_number (int) - primary keyproduct_code (int) - primary keyquantity_ordered (int)price_each (decimal(8,2))
Table: OrdersOrderIDCustomerIDOrderDateAmount11012023-01-01500.0021022023-01-03150.0031012023-01-04200.0041032023-01-07700.0051022023-01-10300.00i. Write an SQL query to count the number of orders.ii. Write an SQL query to calculate the total amount of all orders.iii.Write an SQL query to find the total amount of orders for each customer.iv. Write an SQL query to find the largest order amount.v. Write an SQL query to find customers with a total order amount greater than 500.Answer text Question 3EditViewInsertFormatToolsTableHelp
From the following table, write an SQL query to retrieve the customer ID, order date, and maximum purchase amount for each customer on each order date where the maximum purchase amount exceeds 2000.00. The results should be sorted by customer ID.
Given a table with order details. Please find the table details and sample data below.TABLE NAME: AGG_ORDERS (Table names are case sensitive)FIELD NAMES: ord_no, purchase_amt, ord_date, cust_id, salesman_idWrite a query to find the maximum purchase amount made by each customer from the 'AGG_ORDERS' table.Note: The required input details will be populated in the backend.This is only a sample data.Input format :The input tables are already prepopulated, as given in the problem statement.Output format :The output should display the customer ID and the corresponding maximum purchase amount as shown below.Customer_ID Amount123 788.50278 975.50367 3000.00456 2065.50567 123.25789 2500.00
We will list the customers in this exercise, along with the cumulative value of their orders. This will be similar to exercise 2: we will use aggregation and merging in C# code.The method to implement is IList<Customer> ListCustomers(). The method shall return every customer. The properties of Model.Customer are:Name: the name of the customerZipCode, City, Street: the address fields of the main site of the customerTotalOrders: the cumulative total of all orders of the customer. You have to aggregate the price*amount for each order of a customer to get this total. If a particular customer has no orders, this value shall be null.Follow these steps to solve this exercise:Create and initialize the _customerCollection.List all customers. The customer entity has the list of Sites; the main site is the item MainSiteId points to. Use this value to find the main in among the list.In the collection of the orders, use an aggregation pipeline to calculate the total of all orders for each CustomerId.Finally, you need the "merge" the two result sets. Every customer has a main site; however, not all of them have orders (in which case TotalOrders shall be null).Use the Customers link of the website to test your solution. This will list the data provided by your code in a tabular format. You can use the Add new order functionality from before to create new orders. This must result in an increase in the total for one of the customers.
Upgrade your grade with Knowee
Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.