Re: Lowest 2 items per

From: "Relyea, Mike" <Mike(dot)Relyea(at)xerox(dot)com>
To: Oliveiros d'Azevedo Cristina <oliveiros(dot)cristina(at)marktest(dot)pt>
Cc: <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Lowest 2 items per
Date: 2012-06-01 16:17:50
Message-ID: AF7D9319B29A0242A33C3BF843BD31330EFB8D8D@USA7061MS03.na.xerox.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

> -----Original Message-----
> From: Oliveiros d'Azevedo Cristina
[mailto:oliveiros(dot)cristina(at)marktest(dot)pt]
> Sent: Friday, June 01, 2012 11:21 AM
> To: Oliveiros d'Azevedo Cristina; Relyea, Mike;
pgsql-sql(at)postgresql(dot)org
> Subject: Re: [SQL] Lowest 2 items per
>
> Sorry, Mike, previous query was flawed.
>
> This is (hopefully) the correct version
>
> Best,
> Oliver
>
> SELECT make, model, color,type, subquery1.cpp, min(cost/yield) as
cpp2 (
> SELECT printers.make, printers.model, consumables.color,
> consumables.type, min(cost/yield) AS cpp FROM printers JOIN
> printersandconsumables ON printers.printerid =
> printersandconsumables.printerid JOIN consumables ON
> consumables.consumableid = printersandconsumables.consumableid
> WHERE consumables.cost Is Not Null
> AND consumables.yield Is Not Null
> GROUP BY printers.make, printers.model, consumables.color,
> consumables.type
> ) subquery1
> JOIN
> (
> SELECT printers.make, printers.model, consumables.color,
> consumables.type,cost,yield FROM printers JOIN
printersandconsumables
> ON printers.printerid = printersandconsumables.printerid JOIN
> consumables ON consumables.consumableid =
> printersandconsumables.consumableid
> WHERE consumables.cost Is Not Null
> AND consumables.yield Is Not Null
> ) subquery2
> ON (subquery1.make = subquery2.make
> AND subquery1.model = subquery2.model
> AND subquery1.color = subquery2.color
> AND subquery1.type = subquery2.type)
> WHERE subquery2.cost / subquery2.yield <> subquery1.cpp GROUP BY
> subquery2.make,subquery2. model,
> subquery2.color,subquery2.type,subquery1.cpp
> ORDER BY make, model;
>

Oliver,

I had to make a few grammatical corrections on your query to get it to
run, but once I did it gave me almost correct results. It leaves out
all of the printer models that only have one consumable with a cost.
Some printers might have more than two black inks and some might have
only one. Your query only returns those printers that have two or more.

Here's your query with the corrections I had to make
SELECT subquery2.Make, subquery2.Model, subquery2.Color,subquery2.Type,
subquery1.cpp, min(Cost/Yield) as cpp2
FROM( SELECT Printers.Make, Printers.Model, Consumables.Color,
Consumables.Type, min(Cost/Yield) AS cpp FROM Printers JOIN
PrintersAndConsumables ON Printers.PrinterID =
PrintersAndConsumables.PrinterID JOIN Consumables ON
Consumables.ConsumableID = PrintersAndConsumables.ConsumableID
WHERE Consumables.Cost Is Not Null
AND Consumables.Yield Is Not Null
GROUP BY Printers.Make, Printers.Model, Consumables.Color,
Consumables.Type
) subquery1
JOIN
(
SELECT Printers.Make, Printers.Model, Consumables.Color,
Consumables.Type,Cost,Yield FROM Printers JOIN PrintersAndConsumables
ON Printers.PrinterID = PrintersAndConsumables.PrinterID JOIN
Consumables ON Consumables.ConsumableID =
PrintersAndConsumables.ConsumableID
WHERE Consumables.Cost Is Not Null
AND Consumables.Yield Is Not Null
) subquery2
ON (subquery1.Make = subquery2.Make
AND subquery1.Model = subquery2.Model
AND subquery1.Color = subquery2.Color
AND subquery1.Type = subquery2.Type)
WHERE subquery2.Cost / subquery2.Yield <> subquery1.cpp GROUP BY
subquery2.Make,subquery2.Model,
subquery2.Color,subquery2.Type,subquery1.cpp
ORDER BY Make, Model;

Mike

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Oliveiros d'Azevedo Cristina 2012-06-01 16:28:01 Re: Lowest 2 items per
Previous Message Relyea, Mike 2012-06-01 15:44:21 Re: Lowest 2 items per