Group on ID, and sum from different table (3 table total)

Question

I have three tables,

Name - columns 
Custpackingslipjour(table 1) - (deliverydate, dateclosed,, salesid)
Inventrans (Table 2) -  (itemid, datephysical, transrefid)
Salesline (Table 3) -  (lineamount, itemid, salesid

What i need is a single table containing all three tables, but without redundant information such as multiple of the same salesids, and with a total sum (salesline.lineamount) and where i can elminate results based on deliverydate and closed date, along with datephysical, originally i had this.

SELECT     TOP (10) dbo.AX_CUSTPACKINGSLIPJOUR.DELIVERYDATE,
dbo.AX_SALESLINE.LINEAMOUNT, dbo.AX_INVENTTRANS.DATEPHYSICAL, 
dbo.AX_INVENTTRANS.COSTAMOUNTPOSTED, dbo.AX_INVENTTRANS.ITEMID, 
dbo.AX_INVENTTRANS.TRANSREFID, dbo.AX_CUSTPACKINGSLIPJOUR.SALESID
FROM         dbo.AX_CUSTPACKINGSLIPJOUR
     INNER JOIN
dbo.AX_SALESLINE ON dbo.AX_CUSTPACKINGSLIPJOUR.SALESID = dbo.AX_SALESLINE.SALESID 
     INNER JOIN
     dbo.AX_INVENTTRANS ON dbo.AX_SALESLINE.ITEMID = dbo.AX_INVENTTRANS.ITEMID

But it produces a table with many of the same lines, because of the inherent one-to-many relationship between salesid and itemid.

So I thought i would group the sum of the value of the items associated with each salesid as that is what I am after.

Based on a few other posts on here, I tried combining the sum of lineamount from salesline by grouping it on salesid, but after 5 minutes of running, it did not appear to be a good solution, I think i have the right idea, but I am doing it wrong.

select top 10 s.SALESID,
    SUM(sp.LINEAMOUNT) as 'TotalLineamount'
from AX_CUSTPACKINGSLIPJOUR as s
right outer join AX_SALESLINE as sp on s.SALESID=sp.SALESID 
left outer join AX_INVENTTRANS as p on sp.ITEMID=p.ITEMID
where s.SALESID is not null
group by s.SALESID,sp.LINEAMOUNT

Any help is greatly appreciated and I will follow up any questions or comments if what I am trying to do is unclear.

Edit: Following @coltech's advice and trying with distinct on the salesid, did not work, but it was tested. I inserted the distinct(salesid) after the top 10

Edit: Following Gavins advice, i changed to

select top 10 cp.SALESID,
SUM(sl.LINEAMOUNT) as 'TotalLineamount'
from AX_CUSTPACKINGSLIPJOUR as cp
right outer join AX_SALESLINE as sl on cp.SALESID=sl.SALESID 
left outer join AX_INVENTTRANS as it on sl.ITEMID=it.ITEMID
where cp.SALESID is not null
group by cp.SALESID

I also changed the prefixes for the tables to better match the table names. This works, however if I want to include more columns, for instance. sl.LINEAMOUNT, it.DATEPHYSICAL, it.COSTAMOUNTPOSTED, it.ITEMID, it.TRANSREFID and cp.SALESID

then I will have to include these in the group by, as such.

select top 10 cp.SALESID,
SUM(sl.LINEAMOUNT) as 'TotalLineamount',
cp.DELIVERYDATE,
sl.LINEAMOUNT, it.DATEPHYSICAL, 
it.COSTAMOUNTPOSTED, it.ITEMID, 
it.TRANSREFID, cp.SALESID
from AX_CUSTPACKINGSLIPJOUR as cp
right outer join AX_SALESLINE as sl on cp.SALESID=sl.SALESID 
left outer join AX_INVENTTRANS as it on sl.ITEMID=it.ITEMID
where cp.SALESID is not null
group by cp.SALESID, cp.DELIVERYDATE,sl.LINEAMOUNT, it.DATEPHYSICAL, it.COSTAMOUNTPOSTED, it.ITEMID, it.transrefid

However, this appears to cause the query to run for a very long time, been running for close to 25 mins. Is there a way to speed this up? Or am I just tackling this incorrectly?

@Coltech Well, my understanding of distinct is that it is used to select unique entries in a single table based on a single column. WHat I am trying to accomplish is select all distinct entries of salesid in a combination of three tables, and sum the lineamount for each found salesid. — Tommy, Mar 24 '12 at 15:59

GavinCattell · Accepted Answer · 2012-04-08T13:53:31.177

2

I think you're fine except you shouldn't sum on the column you want to group by, so just do this instead:

select top 10 s.SALESID,
    SUM(sp.LINEAMOUNT) as 'TotalLineamount'
from AX_CUSTPACKINGSLIPJOUR as s
right outer join AX_SALESLINE as sp on s.SALESID=sp.SALESID 
left outer join AX_INVENTTRANS as p on sp.ITEMID=p.ITEMID
where s.SALESID is not null
group by s.SALESID

edited Apr 08 '12 at 13:53

answered Mar 24 '12 at 19:21

GavinCattell

3,863
20
22

This works, great, now what can I do about introducing more columns as reflected in my edits? Thanks for the corrections and comments so far. – Tommy Mar 24 '12 at 23:15
Just add the extra columns to the group by. When grouping you generally don't want to group by anything you're doing a SUM, MIN, MAX, AVG, etc. on. – GavinCattell Mar 25 '12 at 08:11
Based on http://weblogs.sqlteam.com/jeffs/archive/2005/12/14/8546.aspx it would appear that i need to create a subquery to make it work with selecting columns which are not part of an aggregate function, so ill work on that, for now Ill mark this as answered as my original question was answered. Thanks for the help. – Tommy Mar 25 '12 at 09:36
If you list the columns you want and some example data i'll help you out some more. – GavinCattell Mar 25 '12 at 17:38

Group on ID, and sum from different table (3 table total)

1 Answers1