May 2, 2011 at 3:20 pm
I'm having a bad brain day today and can't figure out why my query is returning seperate rows for negative and positive values.
My query looks a little like:
Select
distinct prdid as 'product number',
sum(quano) as 'quantity ordered'
from SalesOrderTable
where prdid = 'XYZ'
This returns each product ordered and the total number of items ordered for that item. The query will eventually return lots of rows, hopefully one per product id. I have a date range set on it so that it doesn't get to crazy. If a customer returns an item it gets entered as a negative quantity. When I run this query I get two lines if there have been returns.
1 XYZ 12
2 XYZ -2
What I would like is to see
1 XYZ 10
Thanks!
Neil Denton
May 2, 2011 at 3:35 pm
Bad brain day indeed. Need to GROUP BY on the productId and sum the quantity. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 2, 2011 at 3:39 pm
Try this:
Select
prdid as 'product number',
sum(quano) as 'quantity ordered'
from SalesOrderTable
where prdid = 'XYZ'
GROUP BY prdid -- Added this now
Guess you have left the GROUP BY clause 😛
May 2, 2011 at 5:44 pm
*Hangs Head in Shame*
*Bangs Head against desk*
It's been a long day. From log files going haywire to tape drives getting tapes stuck in them.
In my original query I did have a group by statement but I got so stuck on the distinct that I didn't even think about it. Using the group by is exactly the answer.
Thanks!
Neil Denton.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply