CODEWITHSUNDEEP
sqloracle-database
user15866126
user15866126

Reputation: 89

Oracle SQL query for displaying customer firstname, Lastname and total invoices from customer and invoice tables

I am trying to display Customer firstname, lastname and total invoices for each customer in my query result. What am I missing? Can someone help? This is the design for the tables: enter image description here

This is what I wrote but getting this error: "FROM keyword not found where expected"

COUNT(INV_NUMBER) AS 'Number of Invoices'
FROM  (SELECT  CUS_CODE, L.INV_NUMBER AS INV_NUMBER
FROM  INVOICE I, LINE L    
WHERE  I.INV_NUMBER = L.INV_NUMBER   
GROUP BY CUS_CODE, L.INV_NUMBER) AS IL 
GROUP BY CUS_CODE;```

Desired result: first name, Last name, Total invoices ordered in descending order

Upvotes: 0

Views: 638

Answers (1)

rgm565
rgm565

Reputation: 66

You need to join the customer and invoice tables by the common element - the customer code.

SELECT c.cus_fname, c.cus_lname, COUNT (*) AS "Inv Count"
FROM   customer c,
       invoice i
WHERE  c.cus_code = i.cus_code
GROUP BY c.cus_fname, c.cus_lname
ORDER BY 3 DESC

Descending order of ... ? Presumably number of invoices. You can't order on the alias but you can by the column position.

Upvotes: 1

Related Questions