ZuoTi.Pro
Question
e

mysql> Select from Orders; | Order ID | Order_Date | Customer_ID | Shipping_Date I ----- --- -- --- | 12489 | 12491 | 12494 |
mysql> Select * from Sales_Representatives; Sales_Rep_ID | Last_Name | First_Name Street Address City State | zip I | 12 | 3
e. For each sales representative, identify the number of customers, total sales of the customers and the number of parts orde
engineering   Computer-Science   
0 0
Add a comment Improve this question Transcribed image text
Next > < Previous
Sort answers by oldest

Homework Answers

Answer #1

select sr.Sales_Rep_ID,sr.Last_Name,sr.First_Name,
sum(case when c.Sales_Rep_ID IS NOT NULL then 1 else 0 end) as Count_Customers,
sum(case when ol.Quoted_Price IS NOT NULL then ol.Quoted_Price*ol.Number_Ordered else 0 end) as Total_Sales,
sum(case when c.Number_Ordered IS NOT NULL then c.Number_Ordered else 0 end) as Total_Parts,
from Sales_Representives sr
LEFT_JOIN Customers c
ON c.Sales_Rep_ID = sr.Sales_Rep_ID
LEFT JOIN Orders o
ON o.Customer_ID = c.Customer_ID
LEFT JOIN Order_line ol
ON ol.Order_ID = o.Order_ID
group by Sales_Rep_ID,Last_Name,First_Name

Hope This Helped!

Please feel free to comment for any concerns or doubts and I try to reply ASAP//

Have a Great Day!

All The Best!

0 0
Add a comment
Know the answer?
Add Answer to:
e mysql> Select from Orders; | Order ID | Order_Date | Customer_ID | Shipping_Date I -----...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions

  • I want write a MySQL query to get the number of customers who have made at...

    I want write a MySQL query to get the number of customers who have made at least one purchase in each group (gender, education), I know how to do with two seperate query. but is that possible to get one query with the column like: gender | counts | education | counts The first 5 rows of each table looks like this: customers customer_id | first_name | last_name | state | birthdate | education | gender | date_account_opened 50 |...

  • DROP TABLE IF EXISTS customers; DROP TABLE IF EXISTS orders; DROP TABLE IF EXISTS order_details; DROP...

    DROP TABLE IF EXISTS customers; DROP TABLE IF EXISTS orders; DROP TABLE IF EXISTS order_details; DROP TABLE IF EXISTS items; DROP TABLE IF EXISTS artists; DROP TABLE IF EXISTS employees; -- create tables CREATE TABLE customers (    customer_id INT ,    customer_first_name VARCHAR(20),    customer_last_name VARCHAR(20) NOT NULL,    customer_address VARCHAR(50) NOT NULL,    customer_city VARCHAR(20) NOT NULL,    customer_state CHAR(2) NOT NULL,    customer_zip CHAR(5) NOT NULL,    customer_phone CHAR(10) NOT NULL,    customer_fax CHAR(10),    CONSTRAINT customers_pk...

  • I need the answer to #2 listed above. This is the code I have come up...

    I need the answer to #2 listed above. This is the code I have come up with, and it is wrong. I am new to this and need help. group by sust.cida Q2 (7 Points) Show customer id (CID) for customers who have spent 30,000 or more with the company in 2019. Money is considered "spent" when the entire work order is completed as recorded in workorder.completionDate. (Correct query will find customer 2). Use ONE SQL statement. select workarder.id from...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT