Using SQL create a table that will give you the result below: This is what i hav

Question

Using SQL create a table that will give you the result below:

This is what i have so far but its giving me the same thing in the Number_of_Invoices column as it's giving me in the Number_of_Products I don't know how to correct it:

select c.cus_lname as Customer_Last_Name, count(i.inv_number) as Number_of_Invoices, count(l.inv_number) as Number_of_Products, round(sum(line_units*line_price),2,0) as Total_Value

from customer c, invoice i, line l

where c.Cus_code = i.Cus_code

and l.inv_number = i.inv_number

group by c.cus_lname

order by Total_Value DESC

Result:

A list of the customer, the total number of orders (invoices) they placed, the total number of products they purchased, and the total value of what they purchased. c. Customer Last Name Number of Invoices Number of Products Total value 443.94 422.71 153.84 0.44 34.95 Dunne 2 6 Smith Farriss O'Brian 2

Explanation / Answer

This is because, you are using inv_number column for number of products as well which shouldn't be the case. You must use prod_number column for number of products. This is you query

select c.cus_lname as Customer_Last_Name, count(i.inv_number) as Number_of_Invoices, count(l.inv_number) as Number_of_Products, round(sum(line_units*line_price),2,0) as Total_Value

from customer c, invoice i, line l

where c.Cus_code = i.Cus_code

and l.inv_number = i.inv_number

group by c.cus_lname

order by Total_Value DESC

Query must be :

select c.cus_lname as Customer_Last_Name, count(i.inv_number) as Number_of_Invoices, count(l.prod_number) as Number_of_Products, round(sum(line_units*line_price),2,0) as Total_Value

from customer c, invoice i, line l

where c.Cus_code = i.Cus_code

and l.inv_number = i.inv_number

group by c.cus_lname

order by Total_Value DESC

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.