Can someone help with the below SQL question? I feel I was close, but I couldn’t
ID: 3864440 • Letter: C
Question
Can someone help with the below SQL question?
I feel I was close, but I couldn’t figure out how to have One entry for “texas instruments” to match its order of 217 in the Order_Detail table. Somehow I got 12,331 rows affected from a table that only had 307 rows??
Write a select statement that will display customer number, customer name and the number of orders belong to the customer. You will need to join two tables, use a COUNT aggregate and a GROUP BY clause in your SELECT statement. Override labels with a more descriptive but brief label.
msc_customer.cust_number AS Cust#, cust_name AS CustName, Count (MSC_Order_detail.ord_quantity) AS OrderQnty from MSC_Customer, MSC_Order_detail GROUP By MSC_Order_detail.ord_quantity, cust_number, cust_name
;
(12331 row(s) affected)
K SQLC ery4, sold -u...(UPympn51 29 (112)) SQL Query 3.sq up Pr (70) SQL Query 2.sql u (UPVmpn5129 (134)* tity) As orderQnty from Msc customer, Msc order detail GROUP By Msc order detail.ord quantity, cust number, cust name c 100% Results Messages Cust# custName OrderQnty 1 1001 Texas Instruments 1 2 1001 Texas Instruments 1 3 1001 Texas Instruments 2 4 1001 Texas Instruments 1 Query executed successfully.Explanation / Answer
Assuming there is cust_number column in both the tables, we need to join the 2 tables on cust_number. So you use inner join on the common field so that you get details of all customers who have orders in the msc_orders table.
Now we need only "Texas Instruments", so we need to filter it by using where clause .
Now since we will get multiple records for same customer with many orders in the msc_orders, we use group by clause to group the results.
So the query would be
SELECT c.cust_number AS CustomerID, c.cust_name AS CustomerName,count(*) AS NumOfOrders
FROM MSC_Customer c INNER JOIN MSC_Order_detail o on c.cust_number= o.cust_number
WHERE c.custName="Texas Instruments"
GROUP BY c.cust_number
Here c is alias for MSC_customer and o is alias for MSC_order . Alias is just a short name for a table used in the query.
The above query will return number of orders by "Texas Instruments".
If you just need all customer names and id with the number of orders they have placed, just leave out the where clause
In case you need any refinement in the query, please give the structure of the 2 tables by issuing
select * from MSC_customer
select * from MSC_order_detail
please take a screenshot of the table structure and paste in question.
Hope the answer helps. Your feedback is valuable. Please leave a feedback and rate the answer if it helped. Thanks.