Show sum of values from 1 table with data from 2 other tables 2

[TheRogueWolf]

Hi, I'm new to SQL queries but I think I've got the hang of the basics. I have a problem which I'm not sure how to proceed with, however, and I'm hoping someone can help.

I have two tables:

Supplier
- suppliernum
- name

OrderDetail
- suppliernum
- orderqty
- unitcost

My aim is to get a list of all suppliers (including suppliers with no orders) showing the total value of orders raised against each one (zero for suppliers with no orders).

Unfortunately, the OrderDetail table holds order lines but doesn't hold a line value, just quantity and unit price for each line on an order.

So, I assume I need to multiply 'orderqty' by 'unitcost' for each line in the OrderDetail table then sum the result by supplier and join that to the Supplier table using 'suppliernum' to display the supplier name.

Could someone let me know if I'm looking at this the right way and maybe suggest an SQL example?

 

[Jamfool]

You will need to do a LEFT JOIN between your supplier table and the orderdetail table to enable you to get all the records from the supplier table even if they havent got any orders against them.

Example of LEFT JOIN syntax:

http://www.w3schools.com/sql/sql_join_left.asp

You will then need to as you said:
'orderqty' * 'unitcost' to get a line total

finally you will need to look at GROUP BY

http://www.w3schools.com/sql/sql_groupby.asp

 

[TheRogueWolf]

Thanks Jamfool, that's a helpful resource. I've written the following code:

SELECT supplier.suppliernum, supplier.supplierid AS 'ID', supplier.name AS 'Name', sum (orderdetail.orderqty * orderdetail.unitcost) AS Total
FROM supplier LEFT JOIN orderdetail
ON supplier.suppliernum = orderdetail.suppliernum
GROUP BY supplier.suppliernum, supplier.supplierid, supplier.name
ORDER BY supplier.suppliernum

Which seems to get me close to what I want but the result is showing multiple duplicate 'suppliernum' results against different 'supplierid' and names.

Any idea where I'm going wrong?

 

[Jamfool]

If suppliernum is your primary key, then what is supplierid?

do you have a few examples of this table:

Supplier
- suppliernum
- name

- supplierid

Example Code: -supplierid

create table #supplier(suppliernum int ,name varchar(50))
create table #orderdetail(suppliernum int,orderqty int,unitcost money)
go
insert into #supplier VALUES (1,'Shop1')
insert into #supplier VALUES (2,'Shop2')
insert into #supplier VALUES (3,'Shop3')
insert into #orderdetail VALUES (1,3,1.00)
insert into #orderdetail VALUES (2,1,50.00)
insert into #orderdetail VALUES (2,5,10.00)

SELECT 
s.suppliernum, 
s.name AS 'Name', 
sum (o.orderqty * o.unitcost) AS Total
FROM #supplier s LEFT JOIN #orderdetail o
ON s.suppliernum = o.suppliernum
GROUP BY s.suppliernum, s.name
ORDER BY s.suppliernum

drop table #orderdetail
drop table #supplier

 

[TheRogueWolf]

My apologies. I've just discovered that there are duplicate suppliernum entries in the suppliers table because different departments use the same suppliernum but for different suppliers (I hasten to add that I didn't design these tables)

I've added a WHERE clause to limit the results to a single department and this seems to have done the trick.

(supplierid is a reference to a code used on a legacy system, btw)

Thanks again for your help!

 

[Jamfool]

np, glad you got it sorted.

 

[TheRogueWolf]

Sorry about this, I have one more question. I now need to select only orders which have been approved.

This is indicated by a field in another table (orderheader.approvalstatus) which could be linked to the orderdetail table using the .ordernum field.

Could you give me a bit more help on how to do this?

Here is my code so far:

SELECT supplier.supplierid AS 'ID', supplier.name AS 'Name', sum (orderdetail.orderqty * orderdetail.unitcost) AS Total
FROM supplier LEFT JOIN orderdetail
ON supplier.suppliernum = orderdetail.suppliernum
WHERE supplier.company = 'SF04'
GROUP BY supplier.supplierid, supplier.name
ORDER BY supplier.name

Thanks again for your help.

 

[Jamfool]

You will need to change your joins slightly to add this in.

Basically inner join orderdetail with orderheader and then use a right outer join back to the supplier table.

Then just add in your approval status criterion.

 

[TheRogueWolf]

Would you be able to give me an example of how to do this please? This is a bit beyond my current SQL abilities

 

[Jamfool]

Here is the join, but try and have a look at is via the gui to see how the join is made up:

SELECT *
FROM         
dbo.orderdetail AS o INNER JOIN
dbo.orderheader AS oh ON o.ordernum = oh.ordernum 
RIGHT OUTER JOIN
dbo.supplier AS s ON o.suppliernum = s.suppliernum

 

[markros]

Alternative solution

SELECT supplier.supplierid AS 'ID', supplier.name AS 'Name', Total
FROM supplier LEFT JOIN 
(select sum (orderdetail.orderqty * orderdetail.unitcost) AS Total, OrderDetail.SupplierNum from
orderdetail where exists (select 1 from OrderHeader where OrderHeader.OrderNum = OrderDetail.OrderNum and OrderHeader.ApprovalStatus = 'A' group by OrderDetail.SupplierNum) OD ON supplier.suppliernum = OD.suppliernum 

WHERE supplier.company = 'SF04'

ORDER BY supplier.name

 

[markros]

Alternative solution:

SELECT supplier.supplierid AS 'ID', supplier.name AS 'Name', Total

FROM supplier LEFT JOIN 
(select sum (orderdetail.orderqty * orderdetail.unitcost) AS Total, OrderDetail.SupplierNum from
orderdetail where exists (select 1 from OrderHeader where OrderHeader.OrderNum = OrderDetail.OrderNum and OrderHeader.ApprovalStatus = 'A') group by OrderDetail.SupplierNum) OD ON supplier.suppliernum = OD.suppliernum 

WHERE supplier.company = 'SF04'

ORDER BY supplier.name

 

[TheRogueWolf]

Thanks markros, that did the trick perfectly.

Thanks again Jamfool, unfortunately I couldn't get your version to work but that's no doubt due to my beginner's SQL skills.

 

[TheRogueWolf]

Back again I'm afraid.

I seem to have a bit of a problem with the code Markros kindly provided. While at first glance it appears to be working correctly, a bit more investigation shows that:

WHERE supplier.company = 'SF04'

doesn't seem to be working as I expected.
Specifically, there is a vendor which has no purchase orders against company 'SF04' but which appears in the results from this query with a 'Total' which is the sum of all the POs on the system, regardless of the company they belong to.

Can anyone help?

 

[markros]

How does supplierNum correspond to Supplier.Company?

I thought SupplierNum uniquely identify the Supplier's Company and therefore restriction on the table from the Left side should only bring correct results.

If this is not the case, when please post some data samples.

You can also add an exists check into derived table sum for supplier's company, e.g.

SELECT supplier.supplierid AS 'ID', supplier.name AS 'Name', Total
FROM supplier LEFT JOIN (select sum (orderdetail.orderqty * orderdetail.unitcost) AS Total, OrderDetail.SupplierNum from
orderdetail where exists 
(select 1 from OrderHeader where OrderHeader.OrderNum = OrderDetail.OrderNum and OrderHeader.ApprovalStatus = 'A') and exists (select 1 from Supplier where WHERE supplier.company = 'SF04' and Supplier.SupplierNum = OrderDetail.SupplierNum) group by OrderDetail.SupplierNum) OD ON supplier.suppliernum = OD.suppliernum WHERE supplier.company = 'SF04'
ORDER BY supplier.name

 

[TheRogueWolf]

Thanks Markros, sorry for the delay in my reply.

It's unfortunate that although I thought that SupplierNum uniquely identified the supplier, this is not the case. The same SupplierNum is used by three different companies (I should mention that these companies are our internal companies/departments, not supplier companies) and therefore there is no unique field!

The SupplierNum and Company fields are present in all the tables used in the query so would it be possible to somehow concatenate these two fields and use that combination to determine the unique records?

Table examples:

Supplier
Company|VendorNum|Name
-------|---------|-----------
SF04 |12 |Jim's Autos
SF03 |12 |Fred's Tyres
etc...

OrderHeader
Company|Vendornum|OrderNum|ApprovalStatus
-------|---------|--------|--------------
SF04 |12 |123456 |A
SF03 |12 |568974 |A
etc...

OrderDetail
Company|VendorNum|OrderNum|OrderQty|UnitCost
-------|---------|--------|--------|--------
SF04 |12 |123456 |12 |1.2
SF04 |12 |123456 |15 |2.1
SF03 |12 |568974 |200 |0.5
etc...

 

[markros]

Try

SELECT supplier.VendorNum AS 'ID', supplier.name AS 'Name', Total
FROM supplier LEFT JOIN (select sum (orderdetail.orderqty * orderdetail.unitcost) AS Total, OrderDetail.VendorNum, OrderDetail.Company from
orderdetail where exists
(select 1 from OrderHeader where OrderHeader.OrderNum = OrderDetail.OrderNum and OrderHeader.ApprovalStatus = 'A') and exists (select 1 from Supplier WHERE supplier.company = 'SF04' and Supplier.VendorNum = OrderDetail.VendorNum and Supplier.Company = OrderDetail.Company) group by OrderDetail.VendorNum, OrderDetail.Company) OD ON supplier.Vendornum = OD.Vendornum  
and Supplier.Company = OD.Company
WHERE supplier.company = 'SF04'
ORDER BY supplier.name

 

[TheRogueWolf]

Thanks again for your invaluable help. I'll try that in the morning and let you know the result...

 

[TheRogueWolf]

That's done the trick, thanks again markros

 

[markros]

You're welcome.