What is the SQL command to list the total sales by region and customer, with subtotals by region and a grand total for all sales?
Added by Janet C.
Step 1
First, we need to group the sales by region and customer. Show more…
Show all steps
Your feedback will help us improve your experience
Akash M and 54 other AP CS educators are ready to help you.
Ask a new question
Labs
Want to see this concept in action?
Explore this concept interactively to see how it behaves as you change inputs.
Key Concepts
Recommended Videos
SQL: Billing Software Report 2 You are working on a billing application and need to get a list of customers with their transaction transcripts. The result should have the following columns: iban / transactions / total. iban - client's IBAN number transactions - list of transaction amount records for a specific IBAN: Record is a transaction amount Records are separated by the sign of the amount of the transaction surrounded by a single space. See 'Expected Output' for an example. Records are sorted in ascending order of dt total - the total amount of all transactions for a specific IBAN. The result should be sorted in descending order by total. Schema Sample Data Tables Expected Output
Madhur L.
Using MySQL, write a query to List all customers who spent 25% or less than the average amount spent on all invoices. For each customer, display their name and the amount spent to 6 decimal places. Order the result by the amount spent from high to low.. There are two tables in the database: 1) customer 2) invoice Example: customer id | customer_name | city_id | customer_address | contact_person | email | phone 1 | Drogerie Wien | 1 | Deckergasse 15A | Emil Steinbach | emil@drogeriewien.com | 094234234 2 | Cosmetics Store | 4 | Watling Street 347 | Jeremy Corbyn | jeremy@c-store.org | 093923923 3 | Kosmetikstudio | 3 | Rothenbaumchaussee 53 | Willy Brandt | willy@kosmetikstudio.com | 0941562222 4 | Neue Kosmetik | 1 | Karlsplatz 2 | NULL | info@neuekosmetik.com | 094109253 5 | Bio Kosmetik | 2 | Motzstraße 23 | Clara Zetkin | clara@biokosmetik.org | 093825825 6 | K-Wien | 1 | Kärntner Straße 204 | Maria Rauch-Kallat | maria@kwien.org | 093427002 7 | Natural Cosmetics | 4 | Clerkenwell Road 14B | Glenda Jackson | glena.j@natural-cosmetics.com | 093555123 8 | Kosmetik Plus | 2 | Unter den Linden 1 | Angela Merkel | angela@k-plus.com | 094727727 9 | New Line Cosmetics | 4 | Devonshire Street 92 | Oliver Cromwell | oliver@nlc.org | 093202404 invoice id | invoice_number | customer_id | user_account_id | total_price 1 | in_25181b07ba800c8d2fc967fe991807d9 | 7 | 4 | 1436 2 | 8fba0000fd456b27502b9f81e9d52481 | 9 | 2 | 1000 3 | 3b6638118246b6bcfd3dfcd9be487599 | 3 | 2 | 360 4 | dfe7f0a01a682196cac0120a9adbb550 | 5 | 2 | 1675 5 | 2a24cc2ad4440d698878a0a1a71f70fa | 6 | 2 | 9500 6 | cbd304872ca6257716bcab8fc43204d7 | 4 | 2 | 150 Expected Output 1 Kosmetikstudio 360.000000 2 Neue Kosmetik 150.000000
Queries: Write the following SELECT statements that return the following to test your work. Write these as CREATE VIEW statements. (Concatenate names with a blank between first and last name) a. Car Seller List: Company name, contact name, street, city, state, zip, phone, fax, ordered by Company Name. b. Vehicle Sales List: Invoice Number, Salesperson name, Approved by Name, VIN, Make, Model, Trade-In Vin, Trade-In Make, Trade-In Model, Selling price, Shipping, Discount, Trade-In Allowance, Subtotal, Taxes, Misc, Total Selling Price (Order by Invoice Number) c. Vehicle Purchase List: Purchase Order Number, Company Name, Contact Name, VIN, Make, Model, Sales Amount, Shipping, Taxes, Total Price, Manager Named. d. Service Invoice List: Invoice Number, Customer Name, VIN, Make, Model, Mileage, Total Service Charge, Total Parts Charge, Total Charges, Taxes, Misc, Total Charges
Aarya B.
Recommended Textbooks
Computer Science and Information Technology
Introduction to Programming Using Python
Computer Science - An Overview
Transcript
18,000,000+
Students on Numerade
Trusted by students at 8,000+ universities
Watch the video solution with this free unlock.
EMAIL
PASSWORD