TSQL Inquiry - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

TSQL Inquiry

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2005-01-28 : 20:00:50

Hi there

I have a table Item like this:

SupplierID INT,
Periode INT,
ProductName VARCHAR(255),
ProductSize VARCHAR(255),
PricePerKg (MONEY)

Basically I have more than 10 supplier and want to pick the cheapest product per kg and know who the cheapest supplier is.

Initially, I ran the following query:

SELECT SupplierID, ProductName , MIN(PricePerKg)
FROM Item
WHERE (PeriodID = 1) AND (PricePerKg > 0)
GROUP BY ProductName

And I got error:
Column 'Item.SupplierID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

So I change this to this:

SELECT SupplierID, ProductName, MIN(PricePerKg)
FROM Item
WHERE (PeriodID = 1) AND (PricePerKg > 0)
GROUP BY SupplierID, ProductName

But it returns like these:

SupplierID, ProductName, PricePerKg
1 Alfafa 9.2857
2 Alfafa 8.9394
3 Alfafa 6.6667
1 Apricots 40-45mm 2.1000
2 Apricots 40-45mm 3.0000
3 Apricots 40-45mm 2.1500

Is not really what I want!!!

Can you guys help? Maybe you can point to the right direction?

Thanks, VV

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-01-28 : 22:27:37

You need to do something like this:


USE Northwind
GO
DROP TABLE Item
GO
CREATE TABLE Item(
	PeriodID INT,
	SupplierID INT,
	ProductName VARCHAR(255),
	PricePerKg MONEY)
GO
INSERT Item(PeriodID, SupplierID, ProductName, PricePerKg)
	SELECT 1, 1, 'Alfafa', 9.2857 UNION ALL
	SELECT 1, 2, 'Alfafa', 8.9394 UNION ALL
	SELECT 1, 3, 'Alfafa', 6.6667 UNION ALL
	SELECT 1, 1, 'Apricots 40-45mm', 2.1000 UNION ALL
	SELECT 1, 2, 'Apricots 40-45mm', 3.0000 UNION ALL
	SELECT 1, 3, 'Apricots 40-45mm', 2.1500

SELECT 
	i1.SupplierID, 
	i2.ProductName, 
	i2.PricePerKg
FROM 
	Item i1
	INNER JOIN (
		SELECT 
			ProductName, 
			MIN(PricePerKg) AS PricePerKg
		FROM 
			Item
		WHERE 
			PeriodID = 1
			AND PricePerKg > 0
		GROUP BY 
			ProductName) i2 ON i1.ProductName = i2.ProductName
		AND i1.PricePerKg = i2.PricePerKg

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

dewacorp.alliances

452 Posts

Posted - 2005-01-29 : 02:10:27

Thanks Derrick

I've tried that one:

SELECT i1.SupplierID, i2.ProductName, i2.PricePerKg
FROM
Item i1
INNER JOIN (
SELECT ProductName, MIN(PricePerKg) AS PricePerKg
FROM Item
WHERE
PeriodID = 1 AND
PricePerKg > 0
GROUP BY ProductName) i2 ON i1.SupplierID = i2.SupplierID

But it returs: Invalid column name 'SupplierID'.

Any other suggestions?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-01-29 : 10:48:52

I edited the entry. The one I had up there definitely would have never worked.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-01-31 : 06:33:02

DerrickLegget, Is this simple?

Select * from Item where PricePerKg in(
Select Min(PricePerKg) from Item group by ProductName)

Madhivanan

Lee-Z
Starting Member

7 Posts

Posted - 2005-01-31 : 07:05:11

I'm not quite sure what the result would be but this:
> Select * from Item where PricePerKg in(
> Select Min(PricePerKg) from Item group by ProductName)

probably won't do it...the subquery would give you for instance:
1.00 (product A)
2.20 (product B)
and the total query would show (for example-sake I assume that all products have the same prices)
product A 1.00
product A 2.20
product B 2.20

Your previous resultset was:
SupplierID, ProductName, PricePerKg
1 Alfafa 9.2857
2 Alfafa 8.9394
3 Alfafa 6.6667
1 Apricots 40-45mm 2.1000
2 Apricots 40-45mm 3.0000
3 Apricots 40-45mm 2.1500

If what you want to see from this is
3 Alfafa 6.6667
1 Apricots 40-45mm 2.1000

Then you should do something like:
select item.supplierid, item.productname, minPrice.pricekg from item
inner join
(select productname, min(pricePerKg) as PriceKg from Item) MinPrice
on item.productname = MinPrice.productname

This:
SELECT i1.SupplierID, i2.ProductName, i2.PricePerKg
FROM
Item i1
INNER JOIN (
SELECT ProductName, MIN(PricePerKg) AS PricePerKg
FROM Item
WHERE
PeriodID = 1 AND
PricePerKg > 0
GROUP BY ProductName) i2 ON i1.SupplierID = i2.SupplierID

Returns: Invalid column name 'SupplierID', because SupplierID is not in i2...if this is what you want, you should add Supplierid to the subquery

dewacorp.alliances

452 Posts

Posted - 2005-02-06 : 05:39:15

Thank you for the input guys.

VV

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources