Fairly advanced MS SQL query

fluiduk

fluiduk

fluiduk

Associate
Joined
13 Nov 2003
Posts
1,567
Location
Manchester
Hi All

I need some help with an sql query for MS SQL.

Basically we have various items that come from multiple sources, ie our own in house stuff, and that from external suppliers. In some instances one product is on both sources. At the moment I have a 'consolidation' script which imports all of the instances into a 'master list'.

What I need now is an sql query that finds all ocurrences of a particular product in a list ( using our product code) and then works out the total stock holding if there is more than 1 source.

Im not sure if this is possible or the best way to do it.

As usual any help is appreciated.

Thanks
Aaron
 
Hi there,

Can you post some very rough schemas of the tables you want to deal with? I'm sure we'll be able to come up with some suggestions but in order to suggest any SQL table and column names would be very helpful :)

arty
 
Hi Arty

Thanks for the reply

There are 2 main tables that are used for this.

The first is the 'master_list' table.

This contains all the product cost and stock information.
It has the following fields.

manf_pn -Manufacturer Part Number
manf - Manufacturer
cost
stock
weight
source - this is a two character representation of the source of the item

In this table are all of the products from all of our suppliers, so a lot are duplicated. However the cost and stock figures may be different. To combat this, we use the lowest price, but we want to combine the total amount of stock from each source as our stock holding.

We then have a product_data table in which all of our product data, spec, image etc is held.

This table is linked to the 'master_list' by the manf_pn

Hope that makes sense lol
Aaron
 
Is that enough info for now?

Also, please feel free to MSN me if that is more convenient

Thanks In Advance
Aaron
 
It sounds like fully normalising the database would be the best way to go, although that might be quite painful to do. One of the resident SQL wizards should be able to sort a query, though :)
 
Hi Beansprout

I think thats the way we will have to go, as the db is getting huge.

However, in the mean time a query from a guru would be great ;)

Aaron
 
I've read over this thread a few times, and can't figure out exactly what you're trying to do... however, from what I can gather you should be looking at a SUM statement?

select manf_pn, SUM(stock) from masterlist where manf_pc = 'AB';

You may want to put a GROUP BY in there too, depending what you're trying to do.

You mention that it's an advanced statement that you're after, which makes me think I've missed something here?, as what i've done is pretty basic.
 
Last edited:
Maybe it just pretty advanced for me lol :p

What I need the query to do is find all records with a particular manf_pn and add together all the stock for those records into 1 value, which I can then display on the page.

Thanks
Aaron
 
Hi Lloydsj

I tried the query you suggested which gave me this error.

'Column masterlist.manf_pn is invalid in the select list because it is not contained in either an aggregate function or the group by clause'

Sadly I have no idea what that means lol, any help is greatly appreciated

Many Thanks
Aaron
 
Code: 
SELECT
	manf_pn,
	manf,
	MIN(cost) AS MinimumCost,
	SUM(stock) AS TotalStockHolding,
	COUNT(DISTINCT source) AS NumberOfSources
FROM master_list
WHERE manf_pn = 123
GROUP BY manf_pn, manf

See how that works; the '123' is your product code, so that query returns the data for just a single product. If the product code is on the other table then you'll need to join onto that - I can give you a hand there too if needed :)

arty
 
You must log in or register to reply here.
Back
Top Bottom