Home » Other » Training & Certification » Help with SUM query..?
Help with SUM query..? Tue, 04 March 2008 22:29
 jlbovo Messages: 8Registered: 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
 Barbara Boehmer Messages: 8709Registered: 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
 Michel Cadot Messages: 64828Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 jlbovo Messages: 8Registered: 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
 Michel Cadot Messages: 64828Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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 May 25 07:39:30 CDT 2017

Total time taken to generate the page: 0.11885 seconds