troubleshooting Question

My Group By Isn't Working

Avatar of jnordeng
jnordeng asked on
Microsoft SQL ServerMicrosoft SQL Server 2008Microsoft SQL Server 2005
7 Comments2 Solutions393 ViewsLast Modified:
Basically we are trying to create two sum columns as you see below and group by productid although I am hoping ot turn it to group by customer or salesman too. When I run this I am not getting the result set expected in teh group by columns. As best as I can explain it the numbers are off. What am I missing? Can you help?

SELECT b.PRODUCT, b.Salesman, a.SalesmanName, a.slsman_region, b.CUSTOMER,b.SHIPTO,
SUM(b.QTYSHIPPED) as QTYSHIP,
SUM(f.UnitsOrdered) as UOrd, ISNULL(c.BeginQty,0) as BeginQty, f.ticketid
FROM CHISM_salesman a
INNER JOIN chinsales2yr b
ON a.slsman_no=b.SALESMAN
LEFT OUTER JOIN CHISM_Allocation_Dealer c
ON c.SalesmanID = b.SALESMAN
LEFT OUTER JOIN CHISM_Allocation_Main d
ON d.ID = c.AllocID
INNER JOIN CHISM_oemain e
ON e.SalesmanId=b.SALESMAN
INNER JOIN CHISM_oeline f
ON f.TicketId=e.TicketId
WHERE
b.SALESMAN = a.slsman_no
and
b.PRODUCT = f.ProductId
and
e.SalesmanId = b.SALESMAN
and
b.CUSTOMER = e.CustomerId and
f.TicketId = e.TicketId and b.shipto = e.ShipToId and
a.slsman_region = 'W' --depending on whether it is admin or salesman looking
and f.Voided = 0
and (e.Stage = 'Entered' Or e.Stage = 'Released')
--date range here
GROUP BY b.Product, b.Salesman, b.customer, f.UnitsOrdered, b.SHIPTO, a.SalesmanName , a.slsman_region, c.BeginQty, d.AllocationName, b.SHIPTO, f.ticketid
ORDER BY b.Product
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros