MySQL: Can/Should my MIN() and MAX() queries be combined?

Question

Here's a simplified version of what I'm currently doing.

A)

SELECT id, foreign_key_id, type, quantity, MAX(price) AS price // <--- Max
FROM quotes
WHERE type = 'tier 1'                                          // <--- Equal
AND prod_id_requested = 12345

B)

SELECT id, foreign_key_id, type, quantity, MAX(price) AS price // <--- Max
FROM quotes
WHERE type != 'tier 1'                                         // <--- NOT equal
AND prod_id_requested = 12345

C)

SELECT id, foreign_key_id, type, quantity, MIN(price) AS price // <--- Min
FROM quotes
WHERE type = 'tier 1'                                          // <--- Equal
AND prod_id_requested = 12345

D)

SELECT id, foreign_key_id, type, quantity, MIN(price) AS price // <--- Min
FROM quotes
WHERE type != 'tier 1'                                         // <--- NOT equal
AND prod_id_requested = 12345

Is there a good way for me to save some resources and maybe combine these into fewer queries?
Or is this pretty much the way to do it?

[UPDATE]
Here's some sample data to play with:

CREATE TABLE IF NOT EXISTS `quote_item` (
  `id` int(2) unsigned NOT NULL AUTO_INCREMENT,
  `quote_id` int(2) unsigned NOT NULL,
  `product_id_quoted` int(3) unsigned NOT NULL,
  `product_id_requested` int(3) unsigned NOT NULL,
  `type` varchar(52) DEFAULT NULL,
  `price` float(10,4) NOT NULL,
  `quantity` int(9) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

INSERT INTO `quote_item` (`id`, `quote_id`, `product_id_quoted`, `product_id_requested`, `type`, `price`, `quantity`) VALUES
(1, 1, 12, 12, 'tier 1', 0.0100, 100),
(2, 2, 1, 12, 'tier 3', 0.0038, 8200),
(3, 2, 13, 12, 'tier 2', 0.0041, 10000),
(4, 3, 7, 14, 'tier 1', 0.0060, 25000),
(5, 3, 8, 12, NULL, 0.0180, 250),
(6, 3, 9, 15, NULL, 0.0019, 5000),
(7, 4, 12, 12, NULL, 0.0088, 7500),
(8, 4, 16, 12, 'tier 1', 0.0040, 10000),
(9, 5, 12, 9, 'tier 2', 0.0089, 1200),
(10, 5, 12, 12, 'tier 1', 0.0072, 6400);

Ultimately, we want to nicely pack up all the data into an array.
Currently, we are getting a row from each query (no GROUPing) where we have the actual ID, Qty, etc... for the single row with the Min value, as well as that same info for the single row with the Max price, per "type" category, then building the array.
For example:

$flash_report = array(
    'tier1' => array(
        'qty' => array(
            'min' => array(
                'id' => 1
                ,'quote_id' => 1
                ,'price' => 0.01
                ,'quantity' => 100
                )
            ,'max' => array(
                'id' => 8
                ,'quote_id' => 4
                ,'price' => 0.004
                ,'quantity' => 10000
                )
            ,'sum' => array(
                'value' => 16500
                ,'count' => 3
                )
        )
        ,'price' => array(
            'min' => array(
                'id' => 8
                ,'quote_id' => 4
                ,'price' => 0.004
                ,'quantity' => 10000
                )
            ,'max' => array(
                'id' => 1
                ,'quote_id' => 1
                ,'price' => 0.01
                ,'quantity' => 100
                )
        )
    )
    ,'other' => array(
        'qty' => array(
            'min' => array(
                'id' => 5
                ,'quote_id' => 3
                ,'price' => 0.018
                ,'quantity' => 250
                )
            ,'max' => array(
                'id' => 3
                ,'quote_id' => 2
                ,'price' => 0.0041
                ,'quantity' => 10000
                )
            ,'sum' => array(
                'value' => 25950
                ,'count' => 4
                )
        )
        ,'price' => array(
            'min' => array(
                'id' => 2
                ,'quote_id' => 2
                ,'price' => 0.0038
                ,'quantity' => 8200
                )
            ,'max' => array(
                'id' => 5
                ,'quote_id' => 3
                ,'price' => 0.018
                ,'quantity' => 250
                )
        )
    )
)

[/UPDATE]

Right now, I'm getting all the data from our individual queries, then assembling it all into a big-ol' array.
I'm thinking there's got to be a better way to do this :)

Answer 1

You can combine it all:

SELECT "equal" as condition,
       MAX(price) AS max_price,
       MIN(price) AS min_price
  FROM quotes
 WHERE type = 'tier 1'
 GROUP BY 1
UNION
SELECT "not_equal" as condition,
       MAX(price) as max_price,
       MIN(price) as min_price
  FROM quotes
 WHERE type != "tier 1"
 GROUP BY 1

I like this better than the CASE solutions (although those are more succinct) because it is completely SQL92 conforming.

I removed the id, foreign_key, and qty fields because I didn't think you intended to have the aggregates grouped on the keys and was wondering why you'd want them grouped on the qty too.

Answer 2

SELECT id, 
       foreign_key_id, 
       type,
       quantity, 
       MAX(case when type = 'tier 1' then price else NULL end) AS price_max_eq,
       MIN(case when type = 'tier 1' then price else NULL end) AS price_min_eq,
       MAX(case when type <> 'tier 1' then price else NULL end) AS price_max_neq,
       MIN(case when type <> 'tier 1' then price else NULL end) AS price_min_neq,
FROM quotes

Answer 3

You can at least combine A and C as well as B and D like so

SELECT id, 
       foreign_key_id, 
       type, 
       quantity, 
       MAX(price) AS max_price, 
       MIN(price) AS min_price
FROM quotes
WHERE type = 'tier 1' 
GROUP BY id, foreign_key_id, type, quantity


SELECT id, 
       foreign_key_id, 
       type, 
       quantity, 
       MAX(price) AS max_price, 
       MIN(price) AS min_price
FROM quotes
WHERE type != 'tier 1' 
GROUP BY id, foreign_key_id, type, quantity

Answer 4

SELECT id, foreign_key_id, type, quantity, 
    MIN(case when type != 'tier 1' then price end) as MinNotTier1Price,
    MIN(case when type == 'tier 1' then price end) as MinTier1Price,
    MAX(case when type != 'tier 1' then price end) as MaxNotTier1Price,
    MAX(case when type == 'tier 1' then price end) as MaxTier1Price
FROM quotes