Home » Other » Training & Certification » Help with SUM query..?
Help with SUM query..? [message #304333] Tue, 04 March 2008 22:29 Go to next message
jlbovo
Messages: 8
Registered: February 2008
Junior Member
Hi, I have another problem that I tried to figure out.

The question was to display the PNAME and the total SALES_V for PID = 12 in all locations in the USA (LOCID = 1,2) for the thrid quarter.

So my query was this...

SELECT P.PNAME, SUM(s.SALES_V)
FROM PRODUCTS4 P, SALES4 S
WHERE P.PID=S.PID
AND P.PID='12'
AND S.LOCID IN ('1', '2')
AND S.TIMEID='3'
GROUP BY P.PNAME;


and I end up getting this, but I know it is wrong because the numbers that I should get back is 20 and 25 = 45. Why am I getting this strange answer ?

PNAME      SUM(S.SALES_V)
---------- --------------
ZORD                   70


Thanks for reading and thanks more if you can help ! - Just
Re: Help with SUM query..? [message #304335 is a reply to message #304333] Tue, 04 March 2008 22:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
What does the following query return?

SELECT P.PNAME,s.SALES_V
FROM PRODUCTS4 P, SALES4 S
WHERE P.PID=S.PID
AND P.PID='12'
AND S.LOCID IN ('1', '2')
AND S.TIMEID='3';
Re: Help with SUM query..? [message #304337 is a reply to message #304333] Tue, 04 March 2008 23:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just a comment: '12', '1', '2', '3' are strings and not numbers.
If your columns are numbers then compare them to numbers and not to strings.

Regards
Michel
Re: Help with SUM query..? [message #304518 is a reply to message #304333] Wed, 05 March 2008 08:23 Go to previous messageGo to next message
jlbovo
Messages: 8
Registered: February 2008
Junior Member
Ha...I ran the query
SELECT P.PNAME,s.SALES_V
FROM PRODUCTS4 P, SALES4 S
WHERE P.PID=S.PID
AND P.PID='12'
AND S.LOCID IN ('1', '2')
AND S.TIMEID='3';



@Barbra,
and I didn't even notice. I guess I was comparing the wrong columns. The result i got was 50 + 20 and that definatly equals 70.

I feel so ashamed to let a little thing like that slide past. Thanks for all the help though.

@ Michel,
When I created the table I only made SALES_V from the SALES4 table with a number. The rest I kept as VARCHAR2 becuase I didn't think I'd be doing and math, except with the SALES_V. Is that how I should of done it ?

Thanks again ! - Just

[Updated on: Wed, 05 March 2008 08:23]

Report message to a moderator

Re: Help with SUM query..? [message #304524 is a reply to message #304518] Wed, 05 March 2008 08:54 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If %ID columns are of type number you must use numbers to compare with them, not strings.

Regards
Michel
Previous Topic: BI Certification
Next Topic: pl/sql
Goto Forum:
  


Current Time: Thu Dec 08 00:25:34 CST 2016

Total time taken to generate the page: 0.10045 seconds