Trying to write a SELECT statement for a homework problem. I added a SUM function but not sure it can be used that way. As well as having 3 JOINs because the tables are distant from each other. Honestly if any of my current code is incorrect please tell me. But my main focus is how to limit the field "discontinued" in the table.

The question is: Draw from the sales data model to prepare a statement showing customer first name, customer e-mail address, product name, and total units, where total units is the sum of units in stock and units on order. Limit records to orders not discontinued (i.e., 'N' from the discontinued field). Create an alias for each field in the SELECT clause, including “Total Units” for the calculated column. Create and use an alias for each table in the FROM clause. Sort by total units.

The table: https://imgur.com/rdZthcH

SELECT customerFN AS “First Name”, customerEmail AS “Email”, productName AS “Product Name”,
(SUM(unitsInStock)+SUM(unitsonorder)) AS "Total Units"
FROM customer c
JOIN order o
ON c.customerID = o.customerID
JOIN orderLine l
ON o.orderID = l.orderID
JOIN product p
ON l.productCode = p.productCode
WHERE 

There is nothing after the WHERE clause because the code is incomplete. The code is incomplete because I believe that I need a WHERE clause of some sort to limit the "discontinued' field. I just don't know how to do that. I've looked up Where clauses and I think it would use >< or !=. But I don't know the function.

2 Answers

1
Bob Jarvis On

It looks to me like you need to add DISCONTINUED = 'N' to your WHERE clause, or in other words

SELECT c.customerFN AS "First Name",
       c.customerEmail AS "Email",
       p.productName AS "Product Name",
       SUM(p.unitsInStock) + SUM(p.unitsonorder) AS "Total Units"
  FROM customer c
  JOIN order o
    ON c.customerID = o.customerID
  JOIN orderLine l
    ON o.orderID = l.orderID
  JOIN product p
    ON l.productCode = p.productCode
  WHERE p.DISCONTINUED = 'N'
  ORDER BY SUM(p.unitsInStock) + SUM(p.unitsonorder)

Also, it looks like you're using a word processor of some sort to create your code because your original code had the fancy open- and close-quote characters (i.e. “ and ” ) instead of ASCII quotation mark characters (i.e. " ). Those word processor-generated characters will not be accepted my most (any?) programming languages or development tools. For example, look at your original question - notice how “First Name” and “Email” aren't highlighted as being strings in the question, but "Total Units" is? That's because only the last one was surrounded by double-quotation-mark characters. I suggest you stick with a plain ASCII editor such as Notepad on Windows, or any of the zillions of programming editor tools out there, and you'll be much happier.

Best of luck.

1
stefan On

When debugging this query, you will most likely encounter several (different) error messages. Also: writing queries without having tables/data at hand can be a bit tricky, as some aspects are easily overlooked. Thus, we'll be using some test tables (including data), see dbfiddle here. The query code below will show you the various errors that you may see - and the SQL required to fix them. Read all the comments in the code.

Relational model (id column names shortened) enter image description here

ORA-00903: invalid table name

In your original query, you are using a table called ORDER. ORDER is a reserved word, see documentation. Avoid using it when naming tables.

ORA-00937: not a single-group group function

When using a valid table name, we can execute the query. Add a condition in the WHERE clause (see below). Due to the SUM()s in the SELECT and ORDER BY clauses, we get the error message. Apparently, we need to add a GROUP BY clause to the query (which immediately leads to the next error message ...)

SELECT 
  c.fn AS "First Name",
  c.email AS "Email",
  p.productName AS "Product Name",
  SUM(p.unitsInStock) + SUM(p.unitsonorder) AS "Total Units" --<- ORA-00937 
FROM customer c
  JOIN orders o     ON c.id = o.id -- table name fixed
  JOIN orderLine l  ON o.id = l.orderid
  JOIN product p    ON l.productCode = p.code
WHERE p.DISCONTINUED = 'N' --<- condition
ORDER BY SUM(p.unitsInStock) + SUM(p.unitsonorder) --<- ORA-00937
;

-- ORA-00937: not a single-group group function
--
-- Cause: A SELECT list cannot include both a group function, 
-- such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, 
-- and an individual column expression, 
-- unless the individual column expression is included in a GROUP BY clause.
-- see:
-- https://docs.oracle.com/cd/B10501_01/server.920/a96525/e900.htm

ORA-00979: not a GROUP BY expression

SELECT 
  c.fn AS "First Name",
  c.email AS "Email",
  p.productName AS "Product Name",
  SUM(p.unitsInStock) + SUM(p.unitsonorder) AS "Total Units"
FROM customer c
  JOIN orders o     ON c.id = o.id
  JOIN orderLine l  ON o.id = l.orderid
  JOIN product p    ON l.productCode = p.code
WHERE p.DISCONTINUED = 'N'
group by p.productname  --<- causes ORA-00979
ORDER BY SUM(p.unitsInStock) + SUM(p.unitsonorder)
;
-- ORA-00979: not a GROUP BY expression
--
-- Make sure that columns ( listed in the SELECT clause ) that are _not_ "aggregated"
-- are listed in the GROUP BY clause, otherwise you may get an ORA-00937. 

Query works - no rows selected!

SELECT 
  c.fn AS "First Name",
  c.email AS "Email",
  p.productName AS "Product Name",
  SUM(p.unitsInStock) + SUM(p.unitsonorder) AS "Total Units"
FROM customer c
  JOIN orders o     ON c.id = o.id
  JOIN orderLine l  ON o.id = l.orderid
  JOIN product p    ON l.productCode = p.code
WHERE p.DISCONTINUED = 'N'
group by p.productname, c.fn, c.email
ORDER BY SUM(p.unitsInStock) + SUM(p.unitsonorder)
;

-- result
no rows selected

Finishing touches

SELECT 
  c.fn AS "First Name",
  c.email AS "Email",
  p.productName AS "Product Name",
  SUM(p.unitsInStock) + SUM(p.unitsonorder) AS "Total Units"
FROM customer C
  JOIN orders O     ON C.id = O.customerid  --<- JOIN condition amended
  JOIN orderLine L  ON O.id = L.orderid
  JOIN product P    ON L.productCode = P.code
WHERE p.DISCONTINUED = 'N'
group by p.productname, c.fn, c.email
ORDER BY "Total Units" --<- you can use the column alias here - instead of SUM(...)
fetch first 10 rows only -- optional
;

-- result
First Name  Email                        Product Name  Total Units  
Annnora     [email protected]            Beans         117          
Arleen      [email protected]  Beans         117          
Gwendolin   [email protected]           Beans         117          
Tomkin      [email protected]         Beans         117          
Alard       [email protected]    Beans         117          
Jeremiah    [email protected]     Beans         117          
Melita      [email protected]             Beans         117          
Stanwood    [email protected]       Beans         117          
Bobbi       [email protected]            Beans         117          
Idelle      [email protected]               Beans         117          


10 rows selected. 

Notice that you can build the query without having INSERTed any rows into the tables beforehand (see dbfiddle). It may be the case that you need to tweak the logic (etc), so that the query fulfils the requirements exactly.