How to Add a Third Table in an SQL Query Containing a SUM

Question

I have the following SQL Query:

SELECT porel.refcode, podetail.ponum, SUM(podetail.orderqty * podetail.unitcost) AS Total
FROM podetail
LEFT JOIN porel ON podetail.ponum = porel.ponum
WHERE porel.poline=podetail.poline AND porel.reftype = 'SPEX'
GROUP BY porel.refcode, podetail.ponum
ORDER BY porel.refcode, podetail.ponum

which works fine to bring me a list of ponums and their total values against refcodes.

I now want to add a third column vendor.name. The vendor table has a field vendorid which it shares with the podetail table.

There are multiple podetail records with the same ponum but these will also have matching vendorid's.

Could someone advise how I would add in the vendor.name field without returning extra rows?

Answer 1

Add it into the GROUP BY. I have assumed that there will be one vendor for each podetail.ponum. By the way your current query is converting the left join to an inner join because of the porel.reftype = 'SPEX' in the Where clause.

SELECT   porel.refcode,
         podetail.ponum,
         SUM(podetail.orderqty * podetail.unitcost) AS Total,
         vendor.name
FROM     podetail
         INNER JOIN vendor ON podetail.vendorid = vendor.vendorid
         LEFT JOIN porel ON podetail.ponum = porel.ponum
WHERE    porel.poline=podetail.poline
AND      porel.reftype = 'SPEX'
GROUP BY porel.refcode,
         podetail.ponum,
         vendor.name
ORDER BY porel.refcode,
         podetail.ponum

Answer 2

SELECT   porel.refcode,
         podetail.poline,
         podetail.ponum,
         SUM(podetail.orderqty * podetail.unitcost) AS Total,
         vendor.name
FROM     podetail
         INNER JOIN vendor ON podetail.vendorid = vendor.vendorid
         LEFT JOIN porel ON podetail.ponum = porel.ponum
WHERE    porel.poline=podetail.poline
AND      porel.reftype = 'SPEX'
GROUP BY porel.refcode,
         podetail.ponum,
         vendor.name
ORDER BY porel.refcode,
         podetail.poline,
         podetail.ponum

I think, you are missing the need to include podetail.poline in your SELECT and GROUP BY.

Answer 3

Since you are aggregating podetails, simply aggregate vendor names:

SELECT porel.refcode, podetail.ponum
, MAX(vendor.name) as VendorName
, SUM(podetail.orderqty * podetail.unitcost) AS Total
FROM podetail
LEFT JOIN vendor on podetail.vendorid = vendor.vendorid
LEFT JOIN porel ON podetail.ponum = porel.ponum
WHERE porel.poline=podetail.poline AND porel.reftype = 'SPEX'
GROUP BY porel.refcode, podetail.ponum
ORDER BY porel.refcode, podetail.ponum

Or use MIN() if you prefer.

Answer 4

Heya, Rogue. I wanted to say a little more than fits in a comment, so here.

One more easy diagnostic off the top of my head is this:

SELECT
    porel.refcode,
    podetail.ponum,
    SUM(podetail.orderqty * podetail.unitcost) AS Total,
    podetail.vendorid
FROM
    podetail
    LEFT JOIN porel
    ON podetail.ponum = porel.ponum
WHERE
    porel.poline = podetail.poline 
    AND porel.reftype = 'SPEX' 
GROUP BY
    porel.refcode, 
    podetail.ponum,
    podetail.vendorid
ORDER BY
    refcode, 
    ponum

In here, we don't join to vendor at all, taking the table completely out of the equation. Instead, we'll just roll up podetail.vendorids instead.

Can you post the output of this query?