how would it be possible to check if a certain value in salesman_id is null, and if it is, assign it to something else (in this case, 0)?
here's what i've wrote up so far:
SELECT O.SALESMAN_ID, SUM(OI.UNIT_PRICE * QUANTITY)
FROM ORDERS O, ORDER_ITEMS OI
GROUP BY O.SALESMAN_ID
ORDER BY O.SALESMAN_ID;
I would suggest:
SELECT COALESCE(O.SALESMAN_ID, 0) as SALESMAN_ID, SUM(OI.UNIT_PRICE * QUANTITY)
FROM ORDERS O JOIN
ORDER_ITEMS OI
ON o.ORDER_ID = OI.ORDER_ID. -- guessing at the relationship
GROUP BY COALESCE(O.SALESMAN_ID, 0)
ORDER BY COALESCE(O.SALESMAN_ID, 0);
Your query as written would produce non-sensical results. Always use proper, explicit, standard, readable JOIN syntax. Never use commas in the FROM clause.
SELECT nvl(O.SALESMAN_ID,0), SUM(OI.UNIT_PRICE * QUANTITY)
FROM ORDERS O, ORDER_ITEMS OI
GROUP BY nvl(O.SALESMAN_ID,0)
ORDER BY 1;
For completeness sake, a further option would be...
SELECT salesman_id, SUM(total)
FROM (
SELECT CASE WHEN o.salesman_id IS NULL
THEN 0
ELSE o.salesman_id as SALESMAN_ID,
OI.UNIT_PRICE * QUANTITY AS total
FROM ORDERS O JOIN
ORDER_ITEMS OI
ON o.ORDER_ID = OI.ORDER_ID
) AS ilv
GROUP BY salesman_id;
But null is a value too....
SELECT CASE WHEN ilv.salesman_id IS NULL
THEN 0
ELSE ilv.salesman_id as SALESMAN_ID,
total
FROM (
SELECT salesman_id
SUM(OI.UNIT_PRICE * QUANTITY) AS total
FROM ORDERS O JOIN
ORDER_ITEMS OI
ON o.ORDER_ID = OI.ORDER_ID
GROUP BY sles,an_id
) AS ilv;
I think the query is incorrect, you need to join orders and ORDER_ITEMS using order_id
scott
case when salesman_id is null then 0 else salesman_id end2.coalesce(salesman_id, 0)And Oracle specific: 3.nvl(salesman_id, 0)4.decode(salesman_id, null, 0, salesman_id)