Select w/ group by question

--000e0cd480b893a4f4048b59c1f8
Content-Type: text/plain; charset=ISO-8859-1

I'm having trouble formulating a query to gather the following data. I can
do this via a script, but now it is more or less just bothering me if there
is an easy/efficient way to gather the following data from a single query.

Example Tables


Products
Type Cost Vendor_id
------------------------------
-----------
apple 1 1
apple 3 2
apple 7 3
pear 2 1
pear 4 2
pear 2 3

Vendors
Vendor_id Vendor_name
--------------------------------------------
1 Walmart
2 Target
3 Kmart


I would like to obtain the least cost of each product type and its
associated vendor.

So...I would like to see a result similiar to the following:

Type Cost Vendor_id Vendor_name
apple 1 1 Walmart
pear 2 1 Walmart
(Note: both vendors 1 and 3 have the same cost which is the lowest. I'm not
really concerned with which vendor is chosen in the result set here.)


If I do: select a.type, min(a.cost), a.vendor_id, b.vendor_name from
products a join vendors b on a.vendor_id = b.vendor_id group by a.type,
a.vendor_id, b.vendor_name all rows are returned because the
type/vendor_id/vendor_name are unique amongst each row. If you remove the
vendor_id and vendor_name from the group by, you get a single row with the
lowest cost for each product, but the vendor_id's and vendor_name's are
incorrect because you are not grouping by them.

Is there a way to do this from a single query. I know I can concat things
together and imbed a select in my where clause to get the result I want, but
this is horribly inefficient.
My real tables have somewhere around 30 million rows in them.

Thanks

Scott

--000e0cd480b893a4f4048b59c1f8--

Re: Select w/ group by question

Scott,

>I would like to obtain the least cost of each product type and its
>associated vendor.

See "Within-group aggregates" at
http://www.artfulsoftware.com/infotree/queries.php.

PB

-----

On 7/14/2010 9:25 AM, Scott Mullen wrote:
> I'm having trouble formulating a query to gather the following data. I can
> do this via a script, but now it is more or less just bothering me if there
> is an easy/efficient way to gather the following data from a single query.
>
> Example Tables
>
>
> Products
> Type Cost Vendor_id
> ------------------------------
> -----------
> apple 1 1
> apple 3 2
> apple 7 3
> pear 2 1
> pear 4 2
> pear 2 3
>
> Vendors
> Vendor_id Vendor_name
> --------------------------------------------
> 1 Walmart
> 2 Target
> 3 Kmart
>
>
> I would like to obtain the least cost of each product type and its
> associated vendor.
>
> So...I would like to see a result similiar to the following:
>
> Type Cost Vendor_id Vendor_name
> apple 1 1 Walmart
> pear 2 1 Walmart
> (Note: both vendors 1 and 3 have the same cost which is the lowest. I'm not
> really concerned with which vendor is chosen in the result set here.)
>
>
> If I do: select a.type, min(a.cost), a.vendor_id, b.vendor_name from
> products a join vendors b on a.vendor_id = b.vendor_id group by a.type,
> a.vendor_id, b.vendor_name all rows are returned because the
> type/vendor_id/vendor_name are unique amongst each row. If you remove the
> vendor_id and vendor_name from the group by, you get a single row with the
> lowest cost for each product, but the vendor_id's and vendor_name's are
> incorrect because you are not grouping by them.
>
> Is there a way to do this from a single query. I know I can concat things
> together and imbed a select in my where clause to get the result I want, but
> this is horribly inefficient.
> My real tables have somewhere around 30 million rows in them.
>
> Thanks
>
> Scott
>
>
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.441 / Virus Database: 271.1.1/3004 - Release Date: 07/14/10 06:36:00
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Select w/ group by question

On Wednesday, July 14, 2010 09:25:22 am Scott Mullen wrote:
> I'm having trouble formulating a query to gather the following data. I can
> do this via a script, but now it is more or less just bothering me if there
> is an easy/efficient way to gather the following data from a single query.
>
> Example Tables
>
>
> Products
> Type Cost Vendor_id
> ------------------------------
> -----------
> apple 1 1
> apple 3 2
> apple 7 3
> pear 2 1
> pear 4 2
> pear 2 3
>
> Vendors
> Vendor_id Vendor_name
> --------------------------------------------
> 1 Walmart
> 2 Target
> 3 Kmart
>
>
> I would like to obtain the least cost of each product type and its
> associated vendor.
>
> So...I would like to see a result similiar to the following:
>
> Type Cost Vendor_id Vendor_name
> apple 1 1 Walmart
> pear 2 1 Walmart
> (Note: both vendors 1 and 3 have the same cost which is the lowest. I'm
> not really concerned with which vendor is chosen in the result set here.)
>
>

Try this:

select name, product_type, min(cost) from vendors join products on
vendors.id = products.vendor_id group by product_type;

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Select w/ group by question

--_eba7ca44-d002-4eb3-95b6-1e5704e0c772_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable




=20


> Date: Wed=2C 14 Jul 2010 10:25:22 -0400
> Subject: Select w/ group by question
> From: smullen27@gmail.com
> To: mysql@lists.mysql.com
>=20
> I'm having trouble formulating a query to gather the following data. I ca=
n
> do this via a script=2C but now it is more or less just bothering me if t=
here
> is an easy/efficient way to gather the following data from a single query=
..
>=20
> Example Tables
>=20
>=20
> Products
> Type Cost Vendor_id
> ------------------------------
> -----------
> apple 1 1
> apple 3 2
> apple 7 3
> pear 2 1
> pear 4 2
> pear 2 3
>=20
> Vendors
> Vendor_id Vendor_name
> --------------------------------------------
> 1 Walmart
> 2 Target
> 3 Kmart
>=20
>=20
> I would like to obtain the least cost of each product type and its
> associated vendor.
>=20
> So...I would like to see a result similiar to the following:
>=20
> Type Cost Vendor_id Vendor_name
> apple 1 1 Walmart
> pear 2 1 Walmart
> (Note: both vendors 1 and 3 have the same cost which is the lowest. I'm n=
ot
> really concerned with which vendor is chosen in the result set here.)
>=20
>=20
> If I do:=20

select a.type=2C min(a.cost)=2C a.vendor_id=2C b.vendor_name from
products a join=20

vendors b=20

on a.vendor_id =3D b.vendor_id=20

group by a.type=2Ca.vendor_id=2C b.vendor_name=20

=20

all rows are returned because the type/vendor_id/vendor_name are unique amo=
ngst each row.=20

=20

If you remove the vendor_id and vendor_name from the group by,

=20

select a.type=2C min(a.cost)=2C a.vendor_id=2Cb.vendor_name from
products a join vendors b=20

on a.vendor_id =3D b.vendor_id=20

group by a.type=3B

=20

you get a single row with the lowest cost for each product,

but the vendor_id's and vendor_name's are incorrect because you are not gro=
uping by them.
>=20
> Is there a way to do this from a single query. I know I can concat things
> together and imbed a select in my where clause to get the result I want=
=2C but
> this is horribly inefficient.
> My real tables have somewhere around 30 million rows in them.

MG>put ascending indexes on vendor_id and vendor_name columns

MG>reorg the tables so the rows will be in vendor_name (within vendor_id) o=
rder

=20

MG>reselect

MG>select a.type=2C min(a.cost)=2C a.vendor_id=2Cb.vendor_name from
MG>products a join vendors b=20

MG>on a.vendor_id =3D b.vendor_id=20

MG>order by a.type=3B


>=20
> Thanks
>=20
> Scott

=20
____________________________________________________________ _____
The New Busy think 9 to 5 is a cute idea. Combine multiple calendars with H=
otmail.=20
http://www.windowslive.com/campaign/thenewbusy?tile=3Dmultic alendar&ocid=3D=
PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_5=

--_eba7ca44-d002-4eb3-95b6-1e5704e0c772_--

Re: Select w/ group by question

--001e680f13b8daf7c5048b5eb1e2
Content-Type: text/plain; charset=ISO-8859-1

Peter

Thanks for the link. I've never run across this page before, but it has
tons of useful information....as well as several answers on how to implement
what I was trying to do.

Ended up going with a solution similar to this example (from the page you
referenced):

SELECT
item,
SUBSTR( MIN( CONCAT( LPAD(price,6,0),supplier) ), 7) AS MinSupplier,
LEFT( MIN( CONCAT( LPAD(price,6,0),supplier) ), 6)+0 AS MinPrice,
SUBSTR( MAX( CONCAT( LPAD(price,6,0),supplier) ), 7) AS MaxSupplier,
LEFT( MAX( CONCAT( LPAD(price,6,0),supplier) ), 6)+0 AS MaxPrice
FROM products
GROUP BY item;

Pretty straight forward and does not require another join back to the same
table with 30+ million rows.

Thanks

Scott

On Wed, Jul 14, 2010 at 10:35 AM, Peter Brawley > wrote:

> Scott,
>
>
> I would like to obtain the least cost of each product type and its
>> associated vendor.
>>
>
> See "Within-group aggregates" at
> http://www.artfulsoftware.com/infotree/queries.php.
>
> PB
>
> -----
>
>
> On 7/14/2010 9:25 AM, Scott Mullen wrote:
>
>> I'm having trouble formulating a query to gather the following data. I
>> can
>> do this via a script, but now it is more or less just bothering me if
>> there
>> is an easy/efficient way to gather the following data from a single query.
>>
>> Example Tables
>>
>>
>> Products
>> Type Cost Vendor_id
>> ------------------------------
>> -----------
>> apple 1 1
>> apple 3 2
>> apple 7 3
>> pear 2 1
>> pear 4 2
>> pear 2 3
>>
>> Vendors
>> Vendor_id Vendor_name
>> --------------------------------------------
>> 1 Walmart
>> 2 Target
>> 3 Kmart
>>
>>
>> I would like to obtain the least cost of each product type and its
>> associated vendor.
>>
>> So...I would like to see a result similiar to the following:
>>
>> Type Cost Vendor_id Vendor_name
>> apple 1 1 Walmart
>> pear 2 1 Walmart
>> (Note: both vendors 1 and 3 have the same cost which is the lowest. I'm
>> not
>> really concerned with which vendor is chosen in the result set here.)
>>
>>
>> If I do: select a.type, min(a.cost), a.vendor_id, b.vendor_name from
>> products a join vendors b on a.vendor_id = b.vendor_id group by a.type,
>> a.vendor_id, b.vendor_name all rows are returned because the
>> type/vendor_id/vendor_name are unique amongst each row. If you remove the
>> vendor_id and vendor_name from the group by, you get a single row with the
>> lowest cost for each product, but the vendor_id's and vendor_name's are
>> incorrect because you are not grouping by them.
>>
>> Is there a way to do this from a single query. I know I can concat things
>> together and imbed a select in my where clause to get the result I want,
>> but
>> this is horribly inefficient.
>> My real tables have somewhere around 30 million rows in them.
>>
>> Thanks
>>
>> Scott
>>
>>
>>
>>
>> No virus found in this incoming message.
>> Checked by AVG - www.avg.com
>> Version: 8.5.441 / Virus Database: 271.1.1/3004 - Release Date: 07/14/10
>> 06:36:00
>>
>>

--001e680f13b8daf7c5048b5eb1e2--