-1

trying to get total sales for a specific zip; I have done a couple of these, but cant seem to get the total and I'm missing something.

Tables are Customers that has the zip, sales that has the gross sale amount.

This one executes complete but no total; so what am I missing?

Also for the sake of asking how could I modify this to ask me in an interface, where I can input the zip? I started that too but; again, I get the question when I insert the zip it gives me errors.

SET SERVEROUTPUT ON

DECLARE
    V_SALES NUMBER (10,2);
    V_ZIP VARCHAR2(5) NOT NULL := 48228;

BEGIN
    SELECT SUM(S.GROSS_SALE_PRICE) -- GROUP FUNCTION
    INTO   V_SALES
    FROM   SALES S
    WHERE  CUST_ID = V_ZIP;

    DBMS_OUTPUT.PUT_LINE ('TOTAL SALES FOR ZIP 48228, IS'|| TO_NUMBER(V_SALES));
END;
/
William Robertson
  • 15,273
  • 4
  • 38
  • 44
Noir
  • 1
  • 2
    `WHERE CUST_ID = V_ZIP` ? Did you mean to join the two tables as part of the query? You haven't included the table structures so can't be sure what the join condition would be (though could guess...) Also not sure why you're using PL/SQL at all here, or why you're mixing data types, or converting a number to a number.... – Alex Poole Apr 20 '17 at 23:05
  • No, actually the zip is part of the customer table, and the gross sale amount is part of the sales table. so, I would have to combine the sales table with the customer table, to get the total sales from a specified zip. I needed to make this a pl/sql anonymous block so this is why I'm using PL/sql in developer. My initial was to use cursor but was trying to find away to simplify it. – Noir Apr 20 '17 at 23:21
  • But your code doesn't include the customer table at all. You seem to know it should. Have you tried to join the two tables? Why did you think you needed to make it an anonymous block? – Alex Poole Apr 20 '17 at 23:25
  • well I did quite notice that; I though the info would come from customer id. because I learn it that way. – Noir Apr 21 '17 at 01:11
  • Please post the structures of the two tables. This will help provide a solutions. I need to know what columns are available in each. – hisnameismyname2 Apr 21 '17 at 06:03
  • Not sure what you mean by 'how could I modify this to ask me in an interface, where I can input the zip?' In PL/SQL Developer you would put it in a Test window and define `zip` as a bind variable in the lower panel. – William Robertson Apr 21 '17 at 16:16

1 Answers1

0

Try including a group by clause

SET SERVEROUTPUT ON
DECLARE
    V_SALES NUMBER (10,2);
    V_ZIP VARCHAR2(5) NOT NULL := 48228;

   BEGIN
SELECT SUM(S.GROSS_SALE_PRICE) -- GROUP FUNCTION
    INTO   V_SALES
    FROM   SALES S
    WHERE  CUST_ID = V_ZIP
    GROUP BY CUST_ID;
DBMS_OUTPUT.PUT_LINE ('TOTAL SALES FOR ZIP 48228, IS'|| TO_NUMBER(V_SALES));
END;
Marcello Manfredini
  • 61
  • 2
  • 8