jnordeng
asked on
My Group By Isn't Working
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Never mind its just stylistic. will check the data when I get to work tomorow.
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
angelli can you do that example you posted with the query I posted originally? A t least partially?
ASKER
A smaller query. I get what you are saying about needing to seperate the SUM columns. I just am having trouble with the syntax. How do I make this group by work with two different tables.
Select a.PRODUCT, c.TicketId, SUM(a.QTYSHIPPED) as QtyS,
SUM(b.QtyShipped) as OES, SUM(a.QTYSHIPPED) - SUM(b.QtyShipped) as [DIFF]
FROM chinsales2yr a --chinsales2yr a
INNER JOIN CHISM_oeline b
ON b.ProductId = a.PRODUCT
INNER JOIN CHISM_oemain c
ON c.TicketId = b.TicketID
WHERE b.Productid = '41A075B1 ' and
b.Voided = 0
and c.Stage = 'Entered'
GROUP BY a.PRODUCT, c.ticketId
ORDER BY a.Product
Select a.PRODUCT, c.TicketId, SUM(a.QTYSHIPPED) as QtyS,
SUM(b.QtyShipped) as OES, SUM(a.QTYSHIPPED) - SUM(b.QtyShipped) as [DIFF]
FROM chinsales2yr a --chinsales2yr a
INNER JOIN CHISM_oeline b
ON b.ProductId = a.PRODUCT
INNER JOIN CHISM_oemain c
ON c.TicketId = b.TicketID
WHERE b.Productid = '41A075B1 ' and
b.Voided = 0
and c.Stage = 'Entered'
GROUP BY a.PRODUCT, c.ticketId
ORDER BY a.Product
ASKER
Thank you all very much.
ASKER